CODEWITHSUNDEEP
sqlms-accessvbams-access-2010
Scorpion99
Scorpion99

Reputation: 248

cannot insert into linked SQL table from an access form

I have a form where I am using VBA code where I defined an STRSQL as Insert into and applying the CurrentDb.Execute STRSQL

It is working fine for local table but when I tried to use it on a linked table, nothing is inserted and I don't get any error message.

Note that if I convert the linked table to local, inserting works fine also.

I have a primary key in my linked table and I am able to fill it manually.

I also noticed that most of my data type in the linked table are as Text and not as Date as defined on SQL server.

The SQL user that I used have writing permissions to the table.

Upvotes: 0

Views: 1309

Answers (1)

Parfait
Parfait

Reputation: 107567

The CurrentDb is a DAO object referencing the current local database. You need to connect the SQL Server tables via DAO ODBC connection to use the db.Execute command.

Alternatively, you can create an append stored query interacting with linked tables and call it with DoCmd.OpenQuery or use your append query VBA string with DoCmd.RunSQL.

Upvotes: 0

Related Questions