<fieldname> is not a valid name when linking SQL table (1 Viewer)

I am a relative novice, attempting to link two SQL tables to an Access 2003 MDB according to the procedure outlined at http://support.microsoft.com/kb/892490/en-us (see code below). The error message I am getting says: “AttachDSNLessTable encountered an unexpected error: ‘account.category’ is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.” The result is that the first table (InvMastr) links just fine, but the second fails. I tried creating a separate module to link just the account table just in case the problem relates to the fact that I am linking both tables at once, but that did not solve the problem. It seems to be something about the SQL table field or field name that is incompatible with Access. It seems strange since the field name is not particularly long, does not contain any funny characters and the InvMastr table (which does link successfully) contains the field name “Category”. The field that is creating the problem is defined in SQL as “char(10), not null”, and the name is “category”.

Any suggestions on why this might occur or how to work around it would be much appreciated.

+++++++++++++++
module code
+++++++++++++++

'//Name : AttachDSNLessTable

'//Purpose : Create a linked table to SQL Server without using a DSN

'//Parameters



Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)

On Error GoTo AttachDSNLessTable_Err

Dim td1 As TableDef

Dim td2 As TableDef

Dim stConnect As String



stLocalTableName1 = "OInvMastr" '// Name of the table that you are creating in the current database

stRemoteTableName1 = "InvMastr" '// Name of the table that you are linking to on the SQL Server database



stLocalTableName2 = "OAccount" '// Name of the table that you are creating in the current database

stRemoteTableName2 = "Account" '// Name of the table that you are linking to on the SQL Server database



stServer = "MAIN\OASISCRM" '// Name of the SQL Server that you are linking to

stDatabase = "DB_OA" '//Name of the SQL Server database that you are linking to

stUsername = "sa" '// Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection

stPassword = "oasiscrm$" '// SQL Server user password



For Each td In CurrentDb.TableDefs

If td.Name = stLocalTableName1 Then

CurrentDb.TableDefs.Delete stLocalTableName1

End If

If td.Name = stLocalTableName2 Then

CurrentDb.TableDefs.Delete stLocalTableName2

End If



Next



If Len(stUsername) = 0 Then

'//Use trusted authentication if stUsername is not supplied.

stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"

Else

'//WARNING: This will save the username and the password with the linked table information.

stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword

End If

Set td1 = CurrentDb.CreateTableDef(stLocalTableName1, dbAttachSavePWD, stRemoteTableName1, stConnect)

Set td2 = CurrentDb.CreateTableDef(stLocalTableName2, dbAttachSavePWD, stRemoteTableName2, stConnect)

CurrentDb.TableDefs.Append td1

CurrentDb.TableDefs.Append td2

AttachDSNLessTable = True

Exit Function



AttachDSNLessTable_Err:



AttachDSNLessTable = False

MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description



End Function

+++++++++++++

Hi there,

Are the owners of the tables both the same?
Have you tried using square brackets? like select * from [account].[category]

I really hope that isn't your actual 'sa' password, it's really not a good idea to post your actual sa password online.