| Author |
Topic |
|
DatabaseStudent
Yak Posting Veteran
71 Posts |
 Posted - 2014-05-30 : 14:42:51
|
| I have a database that is on SQL Server 2012.I have Access connected with an ODBC connectionI have a linked Excel file and a linked table.I am running a query that inserts into the linked table from the Excel file if the record is not already in the table.I am getting timeouts errors #0 when trying to do this and I have tested it in SQL and the insert works in SQL. There is a connection problem that needs to be resolved.Under query properties in Access, I have upped the ODBC timeout from the standard 60, but it is still timing out. It is timing out before ever reaching the new timeout limit so that means that the way to extend the timeout session may be somewhere else. Someone with a lot of experience in using Access and SQL Server should be able to resolve this rather quickly.edit: moved to proper forum |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2014-05-30 : 15:14:18
|
| you should probably post these Access related questions in the Access forum (on this site). You may get more response there.So did you use the ODBC Data Source Administrator to set up this connection? If you go through the configuration wizard you have an opportunity to test the Connection. Did that test succeed?Be One with the OptimizerTG |
 |
|
|
DatabaseStudent
Yak Posting Veteran
71 Posts |
Posted - 2014-05-30 : 16:02:48
|
| Yes, I set up an ODBC connection when I linked the database into Access originally. I can open and edit the tables in the database and they are connected. |
|
|
|
DatabaseStudent
Yak Posting Veteran
71 Posts |
Posted - 2014-05-30 : 19:36:48
|
| Does anyone know how to extend the timeout? The connection is working, but it is just timing out when trying to run the query. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2014-05-30 : 19:44:12
|
| about how long does it take to timeout.The ODBC connection itself has a connection timeout value which defaults to 30 seconds. I imagine Access has a command timeout which is probably what you adjusted. And sql server has a timeout option available but it defaults to no time out.Be One with the OptimizerTG |
|
|
|
DatabaseStudent
Yak Posting Veteran
71 Posts |
Posted - 2014-05-30 : 19:47:54
|
| The query timeout in Access was originally 60. I have made it a lot longer, but the timeout happens before the time that was set.In SQL Server 2012, it remains at 0. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2014-05-30 : 20:21:12
|
| again - how long does it take to timeout? if it is just a few seconds or less then it's not a time out or maybe be a connection timeout. Did you check the ODBC data source configuration? If it's timing out about 30 seconds then that is probably it.Be One with the OptimizerTG |
|
|
|
DatabaseStudent
Yak Posting Veteran
71 Posts |
Posted - 2014-05-30 : 21:10:11
|
| Somewhere between a minutes and two minutes... how do I get to the configuration you are talking about? I thought there only two... one in Access and one in SQL. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2014-05-30 : 23:09:44
|
| I'm not an Access guy so I'm not sure what that one is - it may be another interface to the one I'm talking about. But just hit the windows start button and type in the "search programs and files" for: "Data Sources (ODBC)". That launches the "ODBC Data Source Administrator". Then you have to figure out which one is the one Access is using.Be One with the OptimizerTG |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2014-05-30 : 23:15:46
|
| Another thing you may want to do as a reality if you haven't already is change whatever you need to in the file so that there is a lot less work to do. Just to see if something that should be simple and quick will finish successfully.Be One with the OptimizerTG |
|
|
|
DatabaseStudent
Yak Posting Veteran
71 Posts |
Posted - 2014-05-31 : 02:06:07
|
| I found it and that fixed the issue. Very much appreciate your help. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2014-05-31 : 23:39:01
|
| cool - glad you got it.Be One with the OptimizerTG |
|
|
|
|
Linked Table ODBC Timeouts