0

I have a MS-Access 2010 database that has Sybase tables linked to it through an ODBC named FNA.

I can read from the linked tables just fine, but writing to them is proving to be far more difficult. I am trying to use the below insert into sql to add data from an access table named _ModelVersion to the linked table named DBA__ModelVersion. Sometimes it works for a little while but mainly I get an error that says "ODBC--insert on a linked table 'DBA__Collectors' failed. authentication violation"

strQuery63 = "INSERT INTO [DBA__ModelVersion] ( ID, EDXVersion, template, DatVersion, DbVersion, AccessVersion ) " & _
            "SELECT [_ModelVersion].ID, [_ModelVersion].EDXVersion,[_ModelVersion].ProjectTemplate, [_ModelVersion].DatVersion, [_ModelVersion].DbVersion, [_ModelVersion].AccessVersion " & _
            "FROM _ModelVersion "


DoCmd.SetWarnings False
DoCmd.RunSQL strQuery63
DoCmd.SetWarnings True

Should some other method be used? Not sure it matters but I have combo boxes that use the linked table as a source and before this query is run a dmax function is used on the linked table in question as well. I read that creating a stored procedure on the sybase database side and calling it from access would be the way to go but I have no idea how to do that, much less get the stored procedure to pull data using the FNA on the Access side.

Edit: So far I have tried:
1) making sure data types from sybase are being shown in access properly (data types in sybase are numberic and varchar and in access they show up as decimal and text which seems to be fine)
2) Adding autoincrement primary key to sybase table and indicating it when linking it into access
3) using currentdb.execute with dbseechanges
4) refreshing the table using the method shown in the accepted answer here
5) Tried adding a field timestamp with the value set to current timestamp. No improvement.

Funcionallity remains the same: The update query will occassionally work, then continue giving "run-time error '3155': ODBC--insert on a linked table failed" which is seen whether I use VBA or query design to run the query.

Community
  • 1
  • 1
Bryan
  • 1,801
  • 10
  • 32
  • 55

1 Answers1

0

I've experienced some issues in the past with RunSQL on ODBC linked tables. When dealing with a linked table with a Primary Key, you need to use the dbSeeChanges option.

The error you mention above indicates a table with no primary key set. Right click the table and enter Design view (in MSAccess). Highlight the row you wish to make Primary Key (Unique Identifier). Click the button with the gold key icon.

Try UPDATE/INSERT with the following...

CurrentDb.Execute "INSERT INTO tblYourTableName VALUES ('YourTextValue',123)", dbSeeChanges

Substitute your table/columns/values.

JCro
  • 686
  • 5
  • 10
  • Could you please [edit] in an explanation of why this code answers the question? Code-only answers are [discouraged](http://meta.stackexchange.com/q/148272/274165), because they don't teach the solution. (And in this case, I really don't see how this avoids the authentication errors, nor preserves the insertion from a SELECT.) – Nathan Tuggy Aug 04 '15 at 01:20
  • @NathanTuggy There you go. – JCro Aug 04 '15 at 01:38
  • The CurrentDb.Execute didn't work but adding a numeric auto-increment field to the tables in sybase and making them the primary keys did work, thanks. – Bryan Aug 04 '15 at 15:40
  • Sorry, must rescind. It worked the first time then it started giving me the same issues again. Still stuck in the same boat. – Bryan Aug 04 '15 at 16:20
  • Are you sure that the linked table has had the field marked as primary key in both Access and Sybase? Make sure you use the table relinker after modifying the Sybase table. Another possibility is that you need a TimeStamp column. This page here has a good short intro on it. http://stackoverflow.com/questions/3500703/what-is-the-mysterious-timestamp-datatype-in-sybase I suggest adding a timstamp column and seeing if that helps. I know it does in certain cases when ODBC linking MS SQL tables. – JCro Aug 04 '15 at 22:23
  • Sorry for the delay, but I tried adding a timestamp field that has the value set to current timestamp but it still doesn't work. – Bryan Aug 07 '15 at 16:17
  • Also, I just tried converting the linked table to a local table and tried the insert and I got a failed due to validation error but I don't remember setting up any validation rules and this is the first time I've seen this error. I tried updating each of the columns individually and I still got the error so I'm guessing it isn't a column specific issue. – Bryan Aug 07 '15 at 16:31
  • TimeStamp is misleading, it doesn't actually hold a date/time, you shouldn't need to set any default value to it. Check your indexes and keys to ensure no dependence you're not aware of. – JCro Aug 09 '15 at 22:06
  • Forgot to mention, in addition to above, you _shouldn't_ set any value to the timestamp. – JCro Aug 10 '15 at 03:50
  • I'm accepting this solution again. Those columns probably help in general but the specific solution to my problem ended up being that the copy of sybase I have through my company has an authentication string that is required to write to a sybase database from most sources and I didn't have that string in the ODBC connection. Thank everyone for all of the help. – Bryan Aug 11 '15 at 16:11