cannot insert into SQL linked table from an access form

[scorpion99]

Hi all,

I have a form where I am using the following in my VBA code where I defined an STRSQL as Insert into....
and 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.

What could be the problem? Can someone help?

[ranman256]

Don't use current db.execute....use docmd.runsql

or your external table is not uupdatable.

[pbaldy]

Execute should still work in my experience. Make sure the linked table has a primary key, or it will be read only.

[CJ_London]

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.

just to check - are you linked to the actual table? or a view? if you are seeing field types as text when they are actual dates implies a view which has formatted the dates.

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

Do you mean if you open the linked table directly, you can add new records?

[scorpion99]

@ranman256 ,
I applied your command but I am getting the following and nothing is updated.


@Ajax,
Yes , I can fill it manually on access and see the changes on SQL (It is also a table there). Here are the data fields types on both SQL and access when linked.(Sorry but I had to hide the field names for company confidentiality.

[pbaldy]

What does the finished SQL look like? Try the method here to test it.

[scorpion99]

SQL standard edition 2012 , what method?

[pbaldy]

Sorry, forgot to paste the link:

http://www.baldyweb.com/ImmediateWindow.htm

i meant the SQL of the append query.