467,923 Members | 1,256 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,923 developers. It's quick & easy.

Can't update access table that contains space from Excel VBA

I am developing an application in Ms Excel 2003 that has a feature to update records in Ms Access. The problem is the table name I am trying to update contains a space and whenever I run the below code I got the following Error Message.

[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected ‘DELETE’, ‘INSERT’, ‘PROCEDURE’, ‘SELECT, OR ‘UPDATE’

However, I can’t rename the table because it has many relationships and connections with other tables.

Private Sub CommandButton1_Click()

On Error Resume Next

Dim adoconn As ADODB.Connection
Dim adors As ADODB.Recordset


Set adoconn = New ADODB.Connection
adoconn.Open "Provider=MSDASQL.1;Persist Security Info=False;Data Source=Requirements"


Set adors = New ADODB.Recordset

adors.Open "[EmployeeList1]", adoconn, adOpenDynamic, adLockOptimistic

MsgBox Err.Description
MsgBox Err.Source

Do While Not adors.EOF

If adors.Fields("`Ey Number`").Value = Range("A1").Value Then
adors.Fields("`Ey Number`").Value = "123"

Exit Do

End If
adors.MoveNext
Loop
adors.Update
adors.Close
End Sub



Could you help me to sort out this problem?
Nov 13 '08 #1
  • viewed: 3519
Share:
1 Reply
Expert 512MB
I am developing an application in Ms Excel 2003 that has a feature to update records in Ms Access. The problem is the table name I am trying to update contains a space and whenever I run the below code I got the following Error Message.

[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected ‘DELETE’, ‘INSERT’, ‘PROCEDURE’, ‘SELECT, OR ‘UPDATE’

However, I can’t rename the table because it has many relationships and connections with other tables.

Private Sub CommandButton1_Click()

On Error Resume Next

Dim adoconn As ADODB.Connection
Dim adors As ADODB.Recordset


Set adoconn = New ADODB.Connection
adoconn.Open "Provider=MSDASQL.1;Persist Security Info=False;Data Source=Requirements"


Set adors = New ADODB.Recordset

adors.Open "[EmployeeList1]", adoconn, adOpenDynamic, adLockOptimistic

MsgBox Err.Description
MsgBox Err.Source

Do While Not adors.EOF

If adors.Fields("`Ey Number`").Value = Range("A1").Value Then
adors.Fields("`Ey Number`").Value = "123"

Exit Do

End If
adors.MoveNext
Loop
adors.Update
adors.Close
End Sub



Could you help me to sort out this problem?
Hi

Just a thought, but if "EmployeeList1" is a query (?) then you need this

adors.Open "SELECT * FROM EmployeeList1", adoconn, adOpenDynamic, adLockOptimistic


Also, does your field name actualy have apostrophies, if not, then you do not need then, just

adors.Fields("Ey Number")

should do it.


??


MTB
Nov 13 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Kathleen Turner | last post: by
10 posts views Thread by Randy Harris | last post: by
2 posts views Thread by Acephalus | last post: by
4 posts views Thread by christianlott1 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.