Unable to update/add records in connected SQL Server tables

[JMCS]

I am using the following code to connect to an SQL server database. The
connection 'works' in that the tables are fully visible. However, they are
not update-able which defeats the object.

datasource = "ODBC;Driver={SQL Server};Server=" & SelectServer &
";Database=XXXXX;UID=xxxxxx;PWD=xxxxxx"
Set tbl1 = db.CreateTableDef(tTable, dbAttachSavePWD, srcTable,
datasource)
db.TableDefs.Append tbl1

('SelectServer' is a variable to allow switching between the live and test
databases)

The username with its password has full rights to the database, and manual
connection works fine. Any suggestions on what I'm doing wrong here, please?

JMCS

 

[Stefan Hoffmann]

hi,

I am using the following code to connect to an SQL server database. The
connection 'works' in that the tables are fully visible. However, they are
not update-able which defeats the object.

Access/Jet requires the knowledge of a primary key.

Check the table definition on the SQL Server side, does it contain a
primary key?
Does your linked tables shows the same primary key fields?

You may create an artificial primary key on your linked tables using VBA
for example when your linking SQL Server views instead of tables:

Dim sql As String

sql = "CREATE INDEX pk_TableName " & _
"ON TableName (primaryKeyFields) " & _
"WITH PRIMARY;"
CurrentDb.Execute sql, dbFailOnError

See

http://msdn.microsoft.com/en-us/library/bb177891.aspx

datasource = "ODBC;Driver={SQL Server};Server="& SelectServer&
";Database=XXXXX;UID=xxxxxx;PWD=xxxxxx"
Set tbl1 = db.CreateTableDef(tTable, dbAttachSavePWD, srcTable,
datasource)
db.TableDefs.Append tbl1

Saving the password is a bad habit. You should use Windows
Authentication in the first place.



mfG
--> stefan <--

 

[JMCS]

Stefan

Thank you very much for your input which has all but solved the problem. I
have added the PK as a parameter in my "linkedtables" table. There is still
one problem, however. Whilst all table are connected, the "Create Index"
routine fails for six (out of 131) of them. I have re-checked the PKs, and
there is nothing (such as duplicate records) in the original tables causing
the problem. Any ideas?
(Please note - I tried to post a similar reply earlier, but it failed)

JMCS

 

[JMCS]

Stefan

Apologies to you - I forgot to add the dbfailonerror bit, so I need to
explore this a little more as I am getting different error messages,
depending on the table.

JMCS

 

[JMCS]

Stefan

I can confirm that your suggestion works - most of the error messages were
only confirming that the PK already existed, so not a problem. The last two
were caused by silly table names (not my design!) which contained "-" and
could not be concatenated properly. Thanks very much for your help!

JMCS

(PS - I am not responsible for the password/connection arrangements either)