Run-time Error '3075' ???

[Alp Bekisoglu]

Hi Experts,

There must be something missing but I just can't figure it out. Can someone
poit me in the right direction please?
I get The following error:
Run-time error '3075':
Syntax error (missing operator) in query expression
'M:\udt\MyData.mdb'

when I run the following:

strSQL = "INSERT INTO tb_tmp_p (prop_ad, prop_deger, prop_tip,
prop_tip_ad) " _
& "SELECT " & strA & ", " & strB & ", " & strC & ", " & strD
& ";"
DoCmd.RunSQL strSQL

?strSQL in debug returns:
INSERT INTO tb_tmp_p (prop_ad, prop_deger, prop_tip, prop_tip_ad) SELECT
Name, M:\udt\MyData.mdb, 12, Memo;

The fact is I am trying to store the code returned values for prop_ad,
prop_deger, prop_tip, prop_tip_ad in a table named as tb_tmp_p. Code
actually gets all (available) properties from a given database.

Am I in the wrong park?

Thanks,

Alp

 

[Douglas J. Steele]

If all you're trying to do is insert a row containing known values, there's
an alternate syntax for the INSERT INTO statement:

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

As well, you need to have string delimiters (' or "") around your text
values.

Try:

strSQL = "INSERT INTO tb_tmp_p (prop_ad, prop_deger," _
& " prop_tip, prop_tip_ad) " _
& "VALUES ('" & strA & "', '" & strB _
& "', " & strC & ", '" & strD & "'"
DoCmd.RunSQL strSQL

Exagerated for clarity, that's

strSQL = "INSERT INTO tb_tmp_p (prop_ad, prop_deger," _
& " prop_tip, prop_tip_ad) " _
& "VALUES ( ' " & strA & " ' , ' " & strB _
& " ', " & strC & ", ' " & strD & " ' "
DoCmd.RunSQL strSQL

(I'm assuming that pmp_tip is a numeric field, so I'm not putting quotes
around the value of strC)

If there's a chance that some of the text might contain apostrophes (say, if
the name is Sean O'Driscoll), use

strSQL = "INSERT INTO tb_tmp_p (prop_ad, prop_deger," _
& " prop_tip, prop_tip_ad) " _
& "VALUES (" & Chr$(34) & strA & Chr$(34) & ", " _
& Chr$(34) & strB & Chr$(34) & ", " _
& strC & ", " Chr$(34) & strD & Chr$(34)
DoCmd.RunSQL strSQL

or

strSQL = "INSERT INTO tb_tmp_p (prop_ad, prop_deger," _
& " prop_tip, prop_tip_ad) " _
& "VALUES (""" & strA & """, """ & strB _
& """, " & strC & ", """ & strD & """"
DoCmd.RunSQL strSQL

 

[Alp Bekisoglu]

Thanks Doug for an extensive crash-course! I couldn't have asked for such
detail. I'll let you know when I succeed.

Variable strC (always an integer) determines the type of input for
prop_deger, the other two are always text. Thus I will need to experiment a
bit with your guidance provided. Maybe I should have named strC as varC and
strB as varB and Dim'd them accordingly as well.

Thanks again.

Alp

If all you're trying to do is insert a row containing known values, there's
an alternate syntax for the INSERT INTO statement:

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

As well, you need to have string delimiters (' or "") around your text
values.

Try:

strSQL = "INSERT INTO tb_tmp_p (prop_ad, prop_deger," _
& " prop_tip, prop_tip_ad) " _
& "VALUES ('" & strA & "', '" & strB _
& "', " & strC & ", '" & strD & "'"
DoCmd.RunSQL strSQL

Exagerated for clarity, that's

strSQL = "INSERT INTO tb_tmp_p (prop_ad, prop_deger," _
& " prop_tip, prop_tip_ad) " _
& "VALUES ( ' " & strA & " ' , ' " & strB _
& " ', " & strC & ", ' " & strD & " ' "
DoCmd.RunSQL strSQL

(I'm assuming that pmp_tip is a numeric field, so I'm not putting quotes
around the value of strC)

If there's a chance that some of the text might contain apostrophes (say, if
the name is Sean O'Driscoll), use

strSQL = "INSERT INTO tb_tmp_p (prop_ad, prop_deger," _
& " prop_tip, prop_tip_ad) " _
& "VALUES (" & Chr$(34) & strA & Chr$(34) & ", " _
& Chr$(34) & strB & Chr$(34) & ", " _
& strC & ", " Chr$(34) & strD & Chr$(34)
DoCmd.RunSQL strSQL

or

strSQL = "INSERT INTO tb_tmp_p (prop_ad, prop_deger," _
& " prop_tip, prop_tip_ad) " _
& "VALUES (""" & strA & """, """ & strB _
& """, " & strC & ", """ & strD & """"
DoCmd.RunSQL strSQL



--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hi Experts,

There must be something missing but I just can't figure it out. Can someone
poit me in the right direction please?
I get The following error:
Run-time error '3075':
Syntax error (missing operator) in query expression
'M:\udt\MyData.mdb'

when I run the following:

strSQL = "INSERT INTO tb_tmp_p (prop_ad, prop_deger, prop_tip,
prop_tip_ad) " _
& "SELECT " & strA & ", " & strB & ", " & strC & ", " & strD
& ";"
DoCmd.RunSQL strSQL

?strSQL in debug returns:
INSERT INTO tb_tmp_p (prop_ad, prop_deger, prop_tip, prop_tip_ad) SELECT
Name, M:\udt\MyData.mdb, 12, Memo;

The fact is I am trying to store the code returned values for prop_ad,
prop_deger, prop_tip, prop_tip_ad in a table named as tb_tmp_p. Code
actually gets all (available) properties from a given database.

Am I in the wrong park?

Thanks,

Alp

 

[Alp Bekisoglu]

Got it! Needed to play around a bit as I mentioned before. Now it runs
perfect.
Thanks Doug for taking the time for such a comprehensive explanation.

Alp
Code now looks:
DoCmd.RunSQL "DELETE * FROM tb_tmp_p"

Dim DB As DAO.Database
Dim strA As String, strD As String
Dim varB As Variant, intC As Integer
Dim P_R_P As Property
Dim strSQL As String
filename = GetOpenFile
Set DB = OpenDatabase(filename)
For Each P_R_P In DB.Properties
strA = P_R_P.Name
On Error Resume Next
varB = P_R_P.Value
On Error GoTo 0
intC = P_R_P.Type
strD = FieldTypeName(Val(intC))
If intC = 10 Or intC = 12 Or intC = 15 Then
varB = "'" & varB & "'"
Else
varB = varB
End If
strSQL = "INSERT INTO tb_tmp_p (prop_ad, prop_deger, prop_tip,
prop_tip_ad) " _
& "VALUES ('" & strA & "', " & varB & ", " & intC & ", '" & strD
& "');"
DoCmd.RunSQL strSQL
Next P_R_P
DB.Close
Set DB = Nothing