Create and Setup an ODBC Database Connection
This simple how-to article will explain how to setup an ODBC (Open Database Connectivity) Connection to a SQL Server Database. This connection is most commonly used for a client-server application. For example, if you have a SQL Server Database as a backend and a front-end using something like MS Access.
Thank you for taking the time to report an issue.
What's wrong... Please write below.
I have an Access database that I upsized with the wizard wishing to continue using Access for the Front End. I want to be able to connect to the SQL database without having each computer setup for the DSN. I added this to the autoexec macro/converted to a module.
The error I get is ODBC-Connection failed.
For my testing I did create a DSN on this computer with this user that worked fine. Then when I removed the DSN connection and ran the databaseI got this message, ODBC-Connection failed, again.
I have a MS Access 2007 database that contains a union query that merges 5 different odbc connections. If one connection is down I receive an 'ODBC--call failed' error and the query is unusable.
Is there a way to have the database verify connection and if it cannot connect to one or more odbc, to simply by-pass the odbc(s) and use the others?
I am excited now that I have moved my database to a new hosting company that allows remote connection to my mySql database. I was able to link the tables via ODBC connection. However, I can only view the data for a short period of time before I get that "ODBC --called failed" error message.
I can exit the database and reopen it immediately and the connection is there. What can I do to keep the connection
A DB I've been working on has an ODBC connection for the majority of it's tables. I've now split the DB into a front end and back end application. I understand that the back end requires the ODBC connection to be linked to the tables. My question is does the front end also have to have the ODBC connection? My client has an unusual server setup, and I was wondering if both the front end and back end require the ODBC connection.
I currently have a database that is loading over 2000 spreadsheets each day. The database is opening each file and importing the data. It currently takes the database about 2 1/2 hours to upload all the data.
My manager believes that there is a way to create an ODBC connection to each spreadsheet through VBA. He thinks that the database will run quicker if it can just create a connection to the spreadsheets instead of opening each one.
Is there a way to create an ODBC connection to a multiple csv files through VBA? Can a database really pull information off a spreadsheet without opening it?
We currently run an ODBC connection to Access that is stored on a network drive. We are currently upgrading from a 32 bit computer to 64 bit. Before I simply imported the registry from computer to computer to save time on setting up numerous ODBC connections. Whenever I import onto the new 64 bit system it is importing into the 64 bit ODBC and not into the 32 bit odbc. When I test the connection it is fine but whenever I run the access report it gives the following error:
ODBC--connection to 'database' failed
I am almost certain that it is before the two ODBC connections. My question is will I have to recreate the Access report for 64 bit or is there a way to import on a 64 bit machine to the 32 bit ODBC?
I am not a database guru and limited knowledge of Access and ODBC so please bare with me
I use an ODBC connection to connect my Access database to the "Data Warehouse" at my university (this database was created by someone else years ago and uses Oracle 11g). I just updated to Windows 7 (64-bit) and moved to Access 2010 (though I still have 2007 on my computer). I created a 32-bit ODBC connection (which works fine) but I cannot figure out how to change where my macros go to "look" for the ODBC connection so that I can refresh my report
I currently am storing the user's ID and Password for an ODBC connection in a table. I reference the User info and the connection works fine. Here is my dilemma.We have to reset our passwords every so often and if the user forgets to update their password in my database they will get the ODBC login prompt. I don't want this. I want to throw a simple error message if they have not updated their password, or the password does not work for the ODBC connection. Any tips on how to test? Currently to set up the ODBC I am deleting and re-linking the tables (see below code). Thanks in advance.
DoCmd.DeleteObject acTable, "TableName"
DoCmd.TransferDatabase acLink, "ODBC Database", _
"ODBC;DSN=[Connection];UID=" & strFDOPRD & ";PWD=" & strFDOPRDPass & ";" _
& "DATABASE=pubs", acTable, "TableName", "TableName
I have discovered a seriously annoying problem with Access 2003. I have linked some tables from SQL Server 2005, and if I change a table or if I set the linked tables to go to an odbc connection with another name, I need to link all the tables again. ALL of them, manually. Refresh does not help.
So - my question is:
How do I (if I can) change the odbc connection to go to another database (without changing the name of the connection) from vba
I have a Access front end connected to a MS SQL database using a ODBC connection which is working fine of course.
When I compile it as a runtime version and install it on the business computer I get an error with the ODBC connection (sorry I can't remember exactly what pops up). On the businesscomputer I have installed SQL Native Client and in the ODBC added a System DSN (which works).
is there something I should be doing when saving as runtime?