CODEWITHSUNDEEP
ms-accessvba
Rozha Roz
Rozha Roz

Reputation: 1

Insert image path into table in access by vba

I want to insert image path fields from SQL SERVER into table in access by vba code but it dosent run and send run-time error 3075 , the whole fields insert successful except the path .

  Dim sqlstr As String
  Dim sqlstr1 As String
  dim strSQL as string



 Set rs = CreateObject("ADODB.Recordset")
           rs.CursorType = adOpenDynamic

        rs.LockType = adLockReadOnly
                rs.ActiveConnection = db

                DoCmd.SetWarnings False
                DoCmd.RunSQL "delete from tb_brws"
                DoCmd.SetWarnings True

           rs.Open "SELECT [sno] [imgp] from books_tb  where  [sno] like '" & Me.sno_txt & "' ;"

            rs.MoveFirst

Do
 sqlstr = rs![sno]
 sqlstr1 = rs1![imgp]
 DoCmd.SetWarnings False


   strSQL="insert into tb_brws (sno,imgp) values ('" & sqlstr & "', '" & sqlstr1 & "')"
 DoCmd.RunSQL strSQL

 DoCmd.SetWarnings True

 rs.MoveNext
  Loop While Not rs.EOF



 Set rs = Nothing

Upvotes: 0

Views: 585

Answers (1)

Wayne G. Dunn
Wayne G. Dunn

Reputation: 4312

Thanks for showing the actual SQL string -- it greatly helped because it shows there is a single quote in the string (01-08-2016'123.pdf). Access thinks that is a delimiter (regardless if you enclose in double-quotes). Please use the following code (I have tested and it works just fine).

DoCmd.SetWarnings False
' this will change any ' characters to '' which will allow the SQL to run.
sqlstr1 = Replace(sqlstr1, "'", "''")     ' REPLACE SINGLE QUOTE

strSQL = "insert into tb_brws (sno,imgp) values ('" & sqlstr & "', '" & sqlstr1 & "')"
Debug.Print "MySQL: " & strSQL
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Upvotes: 0

Related Questions