UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Odbc Insert On A Linked Table Failed, Access 2013    
 
   
catbeasy
post Dec 19 2017, 05:41 PM
Post#1



Posts: 469
Joined: 14-March 05



So, I've browsed this forum and the net and not found a satisfactory answer on how to overcome this issue..so will try again..

I've got some SQL in VBA that is supposed to insert a record (two fields are to be placed into their respective fields, the others remain null)* into a backend SQL server table. Some housekeeping:

1) Yes, I have permissions to write to the table.
2) Yes, I can open the table directly (through Access via odbc link) and write to the table.
3) Yes, the SQL server table contains an identity column
4) No, my insert code is not trying to explicitly insert a value into the SQL server table's identity column
5) Yes, the SQL server contains a date/time column (set to default to todays date) - more on this later..
*6) The fields I am NOT inserting data into in the SQL server table are NOT required fields.

Hopefully that covers all the questions I saw in the google search I did on this issue..

So, upon stepping thru my code with the insert, it went thru db.execute without error. However, when checking the back end SQL table, no record!

So, I created an append query in access to see if that would work. For the two (non identity) fields I was trying to insert values into, I got the titular error message. Now, there were several people in the research I did that suggested adding a date/time field to the SQL server table would enable an insert due to some cyber voodoo I could not discern.

My SQL server table already has a date/time field in it (which in the SQL table is defaulted to todays date), so, in the append query, I added a third column to the insert where the value inserted is date(). Then I ran it, then it inserted! I then added this 3rd column date/time insert to my SQL and ran it. Again, no explicit errors thrown, but that method did not update!

I went back to the append query and tried it again and this time, re-got the error! Doh! The irony being that the other posters who were admonished to try this method also said that adding the date/time field to their insert only **sometimes** worked..sigh..

So, short of changing anything on the SQL side, is there anything I can do to make this work? I mean, it did work, once, when I added the date/time to the append. So there must be some way??? Voodoo indeed!

Again, the below SQL code appears fine. It 'runs' fine - goes past the db.execute without issues (note all variables are accounted for giving correct values) - but curiously doesn't throw an error as does the append query..

strSQLID = "Insert Into dbo_dim_templateproject(id_templateproject, DESC_ProjectName, DTTM_Insert) Values " _
& "(" & lngMSAccessTemplateID & "," _
& "'" & strTemplateName & "'," _
& Date & ")"

'excute insertion..
db.Execute strSQLID




Go to the top of the page
 
GroverParkGeorge
post Dec 19 2017, 06:20 PM
Post#2


UA Admin
Posts: 33,038
Joined: 20-June 02
From: Newcastle, WA


Perhaps what you read about was a so-called TimeStamp field?

This is more accurately called a RowVersion field. Calling it TimeStamp was a misstep.

See also this presentation

IF that doesn't resolve the problem, we'll have to dig deeper.

--------------------
Go to the top of the page
 
AlbertKallal
post Dec 19 2017, 07:05 PM
Post#3


UtterAccess VIP
Posts: 2,637
Joined: 12-April 07
From: Edmonton, Alberta Canada


I would add a debug.print strSQLID right before the execute.

Now, take the SQL from the debug window, and paste it right into a blank new query.

Now try running that query – does it work?

My bets are the date format, but try the above – does it work?

The above of course assumes you have a working linked table – and your post suggests this is the case.

I would also suggest you add this to the execute command:

CODE
currentdb.Execute "update dbo_Table1 set Test1 = 'hello'", dbfailonerror


However, the first test or thing would be to do the debug.print trick. So cut + paste what the debug.print shows into a blank new query - try running that and see if it works.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta, Canada
kallal@msn.com


Go to the top of the page
 
catbeasy
post Dec 19 2017, 07:23 PM
Post#4



Posts: 469
Joined: 14-March 05



Thanks GP George..I'll look into adding a rowversion column..

However, wonder why adding that extra column enabled the append query to work and then later not work..? Very odd..

Go to the top of the page
 
GroverParkGeorge
post Dec 19 2017, 08:54 PM
Post#5


UA Admin
Posts: 33,038
Joined: 20-June 02
From: Newcastle, WA


Actually, I think there may be a problem here:

& "'" & strTemplateName & "'," _
& Date & ")"

You're updating a date field, which needs to be properly delimited as a date using the "#" delimiter before and after.

Thanks to Albert for prompting a second, closer, look.
This post has been edited by GroverParkGeorge: Dec 19 2017, 08:56 PM

--------------------
Go to the top of the page
 
jleach
post Dec 20 2017, 03:08 AM
Post#6


UtterAccess Editor
Posts: 9,922
Joined: 7-December 09
From: Staten Island, NY, USA


Also, whenever you run .Execute, include a dbSeeChanges as one of the options: this in conjunction with the ROWVERSION (or in older versions, the horribly named TIMESTAMP) is the mechanism that internally tracks changes between SQL Server and Access to make sure it has the most up to date versions of the records.

--------------------
Go to the top of the page
 
catbeasy
post Dec 20 2017, 02:30 PM
Post#7



Posts: 469
Joined: 14-March 05



Hi Albert et. al

So after adding a rowversion column and messing with the time insert and append query stuff and trying your debug.print suggestion of pasting that into a SQL query window and running..it appears that the append query method did not work all around - which of this, I included the correct insert fields (read on!) ; however, I am embarrassed to say that the VBA code method still didn't after all these changes because, it appears, that I inadvertently DID (despite my saying I DIDN'T!) include as part of the insert the #$%!!# primary key field of the SQL field I am trying to insert to!!! The name is similar to the non PK field, with just a 3 char code at the end to distinguish it..

So, I started chasing my tail and wasted your time for which I humbly apologise. Once I changed the field name to the correct, non PK, field, the query worked. In fact, I then went backwards and took out the rowversion field and it still worked! So, all this for basically nothing (with the exception of not fully understanding why the append query did, then didn't work; which was correct in the field - that my VBA wasn't - but which if it had never worked, I would assume something to do with instant jet engine access for VBA versus the layers the gui has to go thru per the append query?).

Again, my apologies. For those reading this thread..it may still be an issue and an open question - or maybe they fixed it with a new Access (or SQL Server?) version? All the stuff I read historically, the last dated one was 2012 but IIFC, the poster was using Access 2007.

Or maybe its the way our SQL Server folks set up our SQL server or maybe the properties/locks (or lack of) on the SQL table I'm inserting to allow for this type of insert. Not sure..

Just wanted to mea cupla as I didn't want anyone else trying to research this issue get thrown off by my bumbling!

Go to the top of the page
 
GroverParkGeorge
post Dec 20 2017, 08:41 PM
Post#8


UA Admin
Posts: 33,038
Joined: 20-June 02
From: Newcastle, WA


Look at it this way. You got some useful experience troubleshooting. Maybe you learned something along the way. But you definitely didn't waste anyone's time.

Congratulations on solving the problem and good luck with the rest of the project.

--------------------
Go to the top of the page
 
catbeasy
post Dec 21 2017, 01:23 PM
Post#9



Posts: 469
Joined: 14-March 05



Thanks George..!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th July 2018 - 06:22 PM