DoCmd.RunSQL Syntax Help

[scott]

I'm having problems with syntax in a RunSQL statement in PROBLEM PART below.
I get an "incorrect syntax near ':'" and the VBE highlights the RunSQL part.

That's strange since there's no colon in my RunSQL part. It's probably just
missing a quote, but I can't see it.

I posted full code to be safe, but it works fine without the "RunSQL" part.


PROBLEM PART:

DoCmd.RunSQL "INSERT INTO t_data (field1, field2" & _
", field3, field4, field5) VALUES (" & objRS![field1] & ", " &
objRS![field2] & _
", " & Trim(objRS!field3) & ", " & Trim(objRS!field4) & _
", " & Trim(objRS!field5) & ")"

FULL CODE:

Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim sPath As String, sFile As String, sSQL As String

Set objConn = New ADODB.Connection
Set objRS = New ADODB.Recordset

sPath = "C:\databases\"
sFile = "myDBasefile.DBF"

sSQL = "SELECT [Date], [Time], transType, tagName, alarmType FROM
myDBasefile"
objConn.Open "Driver={Microsoft dBASE Driver (*.dbf)};" & _
"DriverID=277;" & _
"Dbq=" & sPath '"Dbq=c:\somepath"

objRS.Open sSQL, objConn, , , adCmdText

Do While Not objRS.BOF And Not objRS.EOF

DoCmd.RunSQL "INSERT INTO mydataTable (field1, field2" & _
", field3, field4, field5) VALUES (" & objRS![field1] & ", " &
objRS![field2] & _
", " & Trim(objRS!field3) & ", " & Trim(objRS!field4) & _
", " & Trim(objRS!field5) & ")"

'Debug.Print objRS.GetString

Loop

 

[scott]

i forgot to mention, this is an ADP file.

 

[Douglas J. Steele]

What's the data types of the fields involved? If any are text, then you're
missing the quotes you need around the data values.

The following assumes that all 5 fields are text:

DoCmd.RunSQL "INSERT INTO mydataTable (field1, field2" & _
", field3, field4, field5) VALUES ('" & objRS![field1] & "', '" & _
objRS![field2] & "', '" & Trim(objRS!field3) & "', '" & _
Trim(objRS!field4) & "', '" & Trim(objRS!field5) & "')"

Exagerated for clarity, that's

DoCmd.RunSQL "INSERT INTO mydataTable (field1, field2" & _
", field3, field4, field5) VALUES ( ' " & objRS![field1] & " ', ' "
& _
objRS![field2] & " ', ' " & Trim(objRS!field3) & " ', ' " & _
Trim(objRS!field4) & " ', ' " & Trim(objRS!field5) & " ' )"

If any of the text values can contain single quotes (apostrophes) in them,
you need to ensure that they're doubled up. Rather than ... '" &
objRS![Field1] & "', ..., you'd want ... '" & Replace(objRS![Field1], "'",
"''") & "', ...

Again, exagerated for clarity, that Replace statement is
Replace(objRS![Field1], " ' ", " ' ' ")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

i forgot to mention, this is an ADP file.

I'm having problems with syntax in a RunSQL statement in PROBLEM PART
below. I get an "incorrect syntax near ':'" and the VBE highlights the
RunSQL part.

That's strange since there's no colon in my RunSQL part. It's probably
just missing a quote, but I can't see it.

I posted full code to be safe, but it works fine without the "RunSQL"
part.


PROBLEM PART:

DoCmd.RunSQL "INSERT INTO t_data (field1, field2" & _
", field3, field4, field5) VALUES (" & objRS![field1] & ", " &
objRS![field2] & _
", " & Trim(objRS!field3) & ", " & Trim(objRS!field4) & _
", " & Trim(objRS!field5) & ")"

FULL CODE:

Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim sPath As String, sFile As String, sSQL As String

Set objConn = New ADODB.Connection
Set objRS = New ADODB.Recordset

sPath = "C:\databases\"
sFile = "myDBasefile.DBF"

sSQL = "SELECT [Date], [Time], transType, tagName, alarmType FROM
myDBasefile"
objConn.Open "Driver={Microsoft dBASE Driver (*.dbf)};" & _
"DriverID=277;" & _
"Dbq=" & sPath '"Dbq=c:\somepath"

objRS.Open sSQL, objConn, , , adCmdText

Do While Not objRS.BOF And Not objRS.EOF

DoCmd.RunSQL "INSERT INTO mydataTable (field1, field2" & _
", field3, field4, field5) VALUES (" & objRS![field1] & ", " &
objRS![field2] & _
", " & Trim(objRS!field3) & ", " & Trim(objRS!field4) & _
", " & Trim(objRS!field5) & ")"

'Debug.Print objRS.GetString

Loop

 

[scott]

How do I deal with datetime types?

What's the data types of the fields involved? If any are text, then you're
missing the quotes you need around the data values.

The following assumes that all 5 fields are text:

DoCmd.RunSQL "INSERT INTO mydataTable (field1, field2" & _
", field3, field4, field5) VALUES ('" & objRS![field1] & "', '" & _
objRS![field2] & "', '" & Trim(objRS!field3) & "', '" & _
Trim(objRS!field4) & "', '" & Trim(objRS!field5) & "')"

Exagerated for clarity, that's

DoCmd.RunSQL "INSERT INTO mydataTable (field1, field2" & _
", field3, field4, field5) VALUES ( ' " & objRS![field1] & " ', ' "
& _
objRS![field2] & " ', ' " & Trim(objRS!field3) & " ', ' " & _
Trim(objRS!field4) & " ', ' " & Trim(objRS!field5) & " ' )"

If any of the text values can contain single quotes (apostrophes) in them,
you need to ensure that they're doubled up. Rather than ... '" &
objRS![Field1] & "', ..., you'd want ... '" & Replace(objRS![Field1], "'",
"''") & "', ...

Again, exagerated for clarity, that Replace statement is
Replace(objRS![Field1], " ' ", " ' ' ")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

i forgot to mention, this is an ADP file.

I'm having problems with syntax in a RunSQL statement in PROBLEM PART
below. I get an "incorrect syntax near ':'" and the VBE highlights the
RunSQL part.

That's strange since there's no colon in my RunSQL part. It's probably
just missing a quote, but I can't see it.

I posted full code to be safe, but it works fine without the "RunSQL"
part.


PROBLEM PART:

DoCmd.RunSQL "INSERT INTO t_data (field1, field2" & _
", field3, field4, field5) VALUES (" & objRS![field1] & ", " &
objRS![field2] & _
", " & Trim(objRS!field3) & ", " & Trim(objRS!field4) & _
", " & Trim(objRS!field5) & ")"

FULL CODE:

Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim sPath As String, sFile As String, sSQL As String

Set objConn = New ADODB.Connection
Set objRS = New ADODB.Recordset

sPath = "C:\databases\"
sFile = "myDBasefile.DBF"

sSQL = "SELECT [Date], [Time], transType, tagName, alarmType FROM
myDBasefile"
objConn.Open "Driver={Microsoft dBASE Driver (*.dbf)};" & _
"DriverID=277;" & _
"Dbq=" & sPath '"Dbq=c:\somepath"

objRS.Open sSQL, objConn, , , adCmdText

Do While Not objRS.BOF And Not objRS.EOF

DoCmd.RunSQL "INSERT INTO mydataTable (field1, field2" & _
", field3, field4, field5) VALUES (" & objRS![field1] & ", " &
objRS![field2] & _
", " & Trim(objRS!field3) & ", " & Trim(objRS!field4) & _
", " & Trim(objRS!field5) & ")"

'Debug.Print objRS.GetString

Loop

 

[scott]

all fields are nvarchar, except for 1 datetime and 1 smallint.

What's the data types of the fields involved? If any are text, then you're
missing the quotes you need around the data values.

The following assumes that all 5 fields are text:

DoCmd.RunSQL "INSERT INTO mydataTable (field1, field2" & _
", field3, field4, field5) VALUES ('" & objRS![field1] & "', '" & _
objRS![field2] & "', '" & Trim(objRS!field3) & "', '" & _
Trim(objRS!field4) & "', '" & Trim(objRS!field5) & "')"

Exagerated for clarity, that's

DoCmd.RunSQL "INSERT INTO mydataTable (field1, field2" & _
", field3, field4, field5) VALUES ( ' " & objRS![field1] & " ', ' "
& _
objRS![field2] & " ', ' " & Trim(objRS!field3) & " ', ' " & _
Trim(objRS!field4) & " ', ' " & Trim(objRS!field5) & " ' )"

If any of the text values can contain single quotes (apostrophes) in them,
you need to ensure that they're doubled up. Rather than ... '" &
objRS![Field1] & "', ..., you'd want ... '" & Replace(objRS![Field1], "'",
"''") & "', ...

Again, exagerated for clarity, that Replace statement is
Replace(objRS![Field1], " ' ", " ' ' ")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

i forgot to mention, this is an ADP file.

I'm having problems with syntax in a RunSQL statement in PROBLEM PART
below. I get an "incorrect syntax near ':'" and the VBE highlights the
RunSQL part.

That's strange since there's no colon in my RunSQL part. It's probably
just missing a quote, but I can't see it.

I posted full code to be safe, but it works fine without the "RunSQL"
part.


PROBLEM PART:

DoCmd.RunSQL "INSERT INTO t_data (field1, field2" & _
", field3, field4, field5) VALUES (" & objRS![field1] & ", " &
objRS![field2] & _
", " & Trim(objRS!field3) & ", " & Trim(objRS!field4) & _
", " & Trim(objRS!field5) & ")"

FULL CODE:

Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim sPath As String, sFile As String, sSQL As String

Set objConn = New ADODB.Connection
Set objRS = New ADODB.Recordset

sPath = "C:\databases\"
sFile = "myDBasefile.DBF"

sSQL = "SELECT [Date], [Time], transType, tagName, alarmType FROM
myDBasefile"
objConn.Open "Driver={Microsoft dBASE Driver (*.dbf)};" & _
"DriverID=277;" & _
"Dbq=" & sPath '"Dbq=c:\somepath"

objRS.Open sSQL, objConn, , , adCmdText

Do While Not objRS.BOF And Not objRS.EOF

DoCmd.RunSQL "INSERT INTO mydataTable (field1, field2" & _
", field3, field4, field5) VALUES (" & objRS![field1] & ", " &
objRS![field2] & _
", " & Trim(objRS!field3) & ", " & Trim(objRS!field4) & _
", " & Trim(objRS!field5) & ")"

'Debug.Print objRS.GetString

Loop

 

[Gunnar Nygaard]

I'm having problems with syntax in a RunSQL statement in PROBLEM PART
below. I get an "incorrect syntax near ':'" and the VBE highlights the
RunSQL part.

That's strange since there's no colon in my RunSQL part. It's probably
just missing a quote, but I can't see it.

I posted full code to be safe, but it works fine without the "RunSQL"
part.


PROBLEM PART:

DoCmd.RunSQL "INSERT INTO t_data (field1, field2" & _
", field3, field4, field5) VALUES (" & objRS![field1] & ", " &
objRS![field2] & _
", " & Trim(objRS!field3) & ", " & Trim(objRS!field4) & _
", " & Trim(objRS!field5) & ")"

FULL CODE:

Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim sPath As String, sFile As String, sSQL As String

Set objConn = New ADODB.Connection
Set objRS = New ADODB.Recordset

sPath = "C:\databases\"
sFile = "myDBasefile.DBF"

sSQL = "SELECT [Date], [Time], transType, tagName, alarmType FROM
myDBasefile"
objConn.Open "Driver={Microsoft dBASE Driver (*.dbf)};" & _
"DriverID=277;" & _
"Dbq=" & sPath '"Dbq=c:\somepath"

objRS.Open sSQL, objConn, , , adCmdText

Do While Not objRS.BOF And Not objRS.EOF

DoCmd.RunSQL "INSERT INTO mydataTable (field1, field2" & _
", field3, field4, field5) VALUES (" & objRS![field1] & ", " &
objRS![field2] & _
", " & Trim(objRS!field3) & ", " & Trim(objRS!field4) & _
", " & Trim(objRS!field5) & ")"

'Debug.Print objRS.GetString

Loop

Just a hint of general usage:

Whenever I build a long string-expression in code and want to check what
doesn't work, I put the string into a textbox in a form so I may pick it up
for review.

strSQL = "INSERT INTO mydataTable (field1, field2, field3, field4, " & _
"field5) VALUES (" & objRS![field1] & ", " & objRS![field2] & _
", " & Trim(objRS!field3) & ", " & Trim(objRS!field4) & _
", " & Trim(objRS!field5) & ")"

Forms!DUMP!Dump = strSQL
DoCmd.RunSQL strSQL



Rgds
Gunnar

 

[Douglas J Steele]

Jet uses # delimiters for dates (and expects the dates to be in mm/dd/yyyy
format, regardless of what your Regional Settings may be). If, for example,
field3 is the date, you'd replace

... ' " & Trim(objRS!field3) & " '...

with

... " & Format(objRS!field3, "\#mm\/dd\/yyyy\#") & " ...

Unfortunately, I don't know what the correct delimiter is for dBase.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

How do I deal with datetime types?

What's the data types of the fields involved? If any are text, then you're
missing the quotes you need around the data values.

The following assumes that all 5 fields are text:

DoCmd.RunSQL "INSERT INTO mydataTable (field1, field2" & _
", field3, field4, field5) VALUES ('" & objRS![field1] & "', '" & _
objRS![field2] & "', '" & Trim(objRS!field3) & "', '" & _
Trim(objRS!field4) & "', '" & Trim(objRS!field5) & "')"

Exagerated for clarity, that's

DoCmd.RunSQL "INSERT INTO mydataTable (field1, field2" & _
", field3, field4, field5) VALUES ( ' " & objRS![field1] & " ', ' "
& _
objRS![field2] & " ', ' " & Trim(objRS!field3) & " ', ' " & _
Trim(objRS!field4) & " ', ' " & Trim(objRS!field5) & " ' )"

If any of the text values can contain single quotes (apostrophes) in them,
you need to ensure that they're doubled up. Rather than ... '" &
objRS![Field1] & "', ..., you'd want ... '" & Replace(objRS![Field1], "'",
"''") & "', ...

Again, exagerated for clarity, that Replace statement is
Replace(objRS![Field1], " ' ", " ' ' ")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

i forgot to mention, this is an ADP file.

I'm having problems with syntax in a RunSQL statement in PROBLEM PART
below. I get an "incorrect syntax near ':'" and the VBE highlights the
RunSQL part.

That's strange since there's no colon in my RunSQL part. It's probably
just missing a quote, but I can't see it.

I posted full code to be safe, but it works fine without the "RunSQL"
part.


PROBLEM PART:

DoCmd.RunSQL "INSERT INTO t_data (field1, field2" & _
", field3, field4, field5) VALUES (" & objRS![field1] & ", " &
objRS![field2] & _
", " & Trim(objRS!field3) & ", " & Trim(objRS!field4) & _
", " & Trim(objRS!field5) & ")"

FULL CODE:

Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim sPath As String, sFile As String, sSQL As String

Set objConn = New ADODB.Connection
Set objRS = New ADODB.Recordset

sPath = "C:\databases\"
sFile = "myDBasefile.DBF"

sSQL = "SELECT [Date], [Time], transType, tagName, alarmType FROM
myDBasefile"
objConn.Open "Driver={Microsoft dBASE Driver (*.dbf)};" & _
"DriverID=277;" & _
"Dbq=" & sPath '"Dbq=c:\somepath"

objRS.Open sSQL, objConn, , , adCmdText

Do While Not objRS.BOF And Not objRS.EOF

DoCmd.RunSQL "INSERT INTO mydataTable (field1, field2" & _
", field3, field4, field5) VALUES (" & objRS![field1] & ", " &
objRS![field2] & _
", " & Trim(objRS!field3) & ", " & Trim(objRS!field4) & _
", " & Trim(objRS!field5) & ")"

'Debug.Print objRS.GetString

Loop