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.