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

[Esmi]

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?

[MikeTheBike]

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