November 25, 2011 at 6:37 am
Hi All,
I'm have a database using Access 2010 as a front end and Microsoft SQL 2008 R2 as a back end via ODBC.
The DB was previously all in Access and I upsized to SQL which seemed to have happened sucessfully!
My problem now is that when I try to access the reports via the Access front end I get an ODBC connection error saying: ODBC- connection to *DBName*(SQL DB) failed.
Any help would be greatly appreciated!
November 25, 2011 at 10:49 am
Can you open the linked tables in Access?
Can you create a new view using the linked tables?
November 28, 2011 at 2:23 am
Hi SSC,
I can see the linked tables in Access and can create views using them.
R
November 28, 2011 at 12:42 pm
Then it seems like there hass been a connection to SQL Server data before and the front end still using this "old" connection. Maybe it's even a connection to a different SQL source.
You'll need to find the part of the code/report that's using the ODBC connection. I don't have any other advice. I'm sorry.
November 29, 2011 at 2:38 am
Hi,
I sorted that error out by undoing all I had previously done i.e upsizing using the Access database tool and redoing it using SSMA for Access and it now throws a
'Run-time error 3073: Operation must use an updateable query' error.
I am leaving the Access front end as is and hoping to write to tables in SQL 2005. Problem is parts of the Access reports run sucessfully and other dont (one throws the aforementioned error and the other crashes) seeing as the bit with the error message tries to do an update in SQL I assumed it might have to do with the permissions on SQL ... this dosen't seem to be the case.
Any ideas?
Thank you!
R
November 29, 2011 at 7:22 am
There are several possible reasons for this error message:
1) When using the ACCESS Wizard to link the table, did you mark the identity columns for each table? If not, it might throw this error because the JET Engine cannot identify the rows to be updated
2) Are there any subqueries or the like used in the update statement? If that's the case, JET DB Engine might treat this query with a subquery as non-updateable. You'll need to rewrite the logic then.
3) Does the update query return an error if run in SSMS (after the required syntax modification, of course)? If so, what error is it? The query might be written in a way that's not valid in SQL Server.
I'd go the following route:
Make the query runable in SQL Server (first as a plain SELECT, then as an UPDATE query). Save the SELECT query as a view, link it to ACCESS and provide the identity columns.
Then write a simple update query using the view only. If it works, modify your code based on the view in SQL Server.
I did a back-end migration from ACCESS to SQL Server a couple month ago and had the same issue. I ended up with modifying the SQL Server data types after the migration, then verified each table had a unique clustered index and re-imported each table just to make sure the identity columns were "transferred".
If the system would have been based on windows authentication, I would have used functions and stored procedures. But since this wan't the case, I decided against it due to the connection string being visible in plain text...
December 1, 2011 at 12:08 pm
As a further comment on what Lutz posted. An updatable query in Access may not be updatable when converted to a view on the SQL Server side. When you link the view in Access you have give it a unique key (primary key) or it won't be updatable.
Todd Fifield
December 1, 2011 at 12:16 pm
tfifield (12/1/2011)
As a further comment on what Lutz posted. An updatable query in Access may not be updatable when converted to a view on the SQL Server side. When you link the view in Access you have give it a unique key (primary key) or it won't be updatable.Todd Fifield
Which is pretty much what I meant with
Save the SELECT query as a view, link it to ACCESS and provide the identity columns.
😉
I can't remember though whether ACCESS will ask for identiy column(s) or unique/primary key.
December 1, 2011 at 1:01 pm
Lutz,
As I recall it just asks for the columns for a unique/primary key.
Todd
December 2, 2011 at 3:13 pm
An Access report requires only a SELECT statement to retrieve the data, so there must be some VBA code that runs when you launch the report - and that code is running an UPDATE statement that fails. You could place a breakpoint in the Click event and then single-step (F8) until you find the offending statement.
Sometimes in an Access app, you will collect / summarize data into a temporary table to speed-up a report. If that is the case, you might try moving that temporary table back into the Access front-end.
December 5, 2011 at 2:57 am
Thanks for your input all!
I found the offending bits COP last week .... it was a bit of VBA code which was trying to update one of the tables. As suggested by Ten Centuries I simply unlinked that table from it's SQL destination and left it within Access. This worked!
Thanks again all .... this is a work in progress and I am straight unto another 'feature' as we speak.
R
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply