cannot insert into linked SQL table from an access form

Question

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.

Parfait · Accepted Answer · 2015-04-05 01:50:47Z

0

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.

answered Apr 5, 2015 at 1:50

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Parfait Over a year ago

I mention how in my answer: 1) create a new append query object scripted like your VBA string (minus variables and outside quotes), save the query, and call it in VBA DoCmd.OpenQuery qryname or 2) run the command DoCmd.RunSQL strsql in VBA. You may need to Set Warnings False to avoid append query warning. For more info, Google the commands.

Collectives™ on Stack Overflow

cannot insert into linked SQL table from an access form

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related