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.

Sponsored Links:

Related Topics

ODBC-Connection failed to sublist
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.

[code]

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.


ODBC connection and splitting the DB
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.


bypass odbc connection
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?


Keep losing ODBC connection to mySql
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


Creating an ODBC Connection in VBA
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?


ODBC 32 to 64 Bit connection error
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


Updated to 2010, created a new System ODBC - how can I change the old ODBC connection
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


Test for valid ODBC connection
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


How to configure odbc-connection from vba-code
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


Access Runtime and ODBC connection can't get it to work..
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?


Test ODBC connection
I was wondering if its possible to test my ODBC connection using my DSNLess connection to my remote database without creating a query or table link?

Is there a sample VBA code I can look at


ODBC connection Access and Oracle
In a access database I have a connection between MS Access and a Orcale database. Once in a while the connection dies. Does anyone know a vba code that the connection test and possibly re-create the connection as needed.


Multiple ODBC Database Connections?
Not sure if anyone has come accross this. I have an Access (2002) Application that is used in conjunction with a DB2 Database Server through an ODBC connection.

Step 1 created a Global Database connection


ODBC, trusted connection for multiple users
I have an Access DB with a few ODBC links to some tables in an external 3rd party My SQL DB. I'm able to use a similar ODBC connection to this same SQL db with Crystal Reports and multiple users have no problem running their Crystal Reports.

However, Access is only allowing me to configure one PC client to have the ODBC trusted connection work. if you try to configure a 2nd PC, it becomes the one with the allowed connection, and the 1st PC no longer works.

Can anyone tell me the clue to making ODBC using trusted connections work for multiple users in the same database


Sharing an ODBC Connection
I have an MS Access application that includes obtaining data from an SQL backend database. I have defined an ODBC connection to the SQL database and everything works fine on my system. I have a number of queries that include code like this "c:\documents and settings\<userid>\My Documents\<odbc database name>". I want to share this application with other members on my team. I understand that they have to set up their own ODBC connection and this is normally stored in their own My Documents folder. How do I modify the code sample above so that it is no longer specific to a <userid> \\My Documents folder? I tried copying the ODBC database to a shared folder but other people who try to access it can't open the tables in the SQL backend database when executing a query.


Question Changing SQL user pw causes ODBC connection issues
I have a network that I am running with a SQL 2008 R2 server where I want to change the pw on the user associated with a SQL DB that my Access 2003 database accesses. I changed the pw on the SQL user and when I go into the ODBC connection in Admin tools I get a successful connection with the new pw, but when I try to open the Access database that is using this DSN I get an ODBC connection error. I tried relinking the tables with the Link Table Manager, but I still seem to have this issue.

What do I need to do in order to change a SQL user so that the ODBC connection for my Access database works? The SQL DB is running SQL 2008 R2 running on Server 2008 R2 and the client machines are running Access 2003 on Windows XP SP3.


How do You set up a DSN Less Connection
I have a Access Database with 2 linked tables that are linked to SQL Server. At some point I want to be able to distribute this database to a couple other locations without having to create an odbc on the users end. How can I setup up a DSN Less connection that will connect the 2 tables to SQL Server?


ODBC connection
I have an issue regarding creating ODBC connection to an oracle DB.
I need to create reports that use different tables (stored on Oracle DB). this access will be placed in several machine and users will users will use it to print out the reports with up-to-date data.

I've search about how to create an ODBC connection from the admin tools; but things not ending as expected.
If someone gives me details steps how to create an ODBC connection so my access program will be able to connect to the oracle db to retrieve the up-to-date data, I will really appreciate it.

assuming that my oracle db username:guest; password: welcome
what is the required steps to do that? what are the things required to be done to keep the access file working in any machine?


ODBC connection
I have an issue regarding creating ODBC connection to an oracle DB. I need to create reports that use different tables (stored on Oracle DB). this access will be placed in several machine and users will users will use it to print out the reports with up-to-date data.

I've search about how to create an ODBC connection from the admin tools; but things not ending as expected.

If someone gives me details steps how to create an ODBC connection so my access program will be able to connect to the oracle db to retrieve the up-to-date data,

assuming that my oracle db username:guest; password: welcome what is the required steps to do that? what are the things required to be done to keep the access file working in any machine?


ODBC connection and running something every hour or so
I have more than 1 question and not sure if I should open a thread for each question or if combining them is ok.

if I shouldnt've combined them - I'm sorry.

1. is there a way to create an ODBC connection by code? so that I don't have to go on every computer and create the ODBC connection (I'm talking about going to the window below in case I'm using wrong words to describe it)

2. how do I run some code inside in Access db every few hours without using Windows Task Manager to open a database that has an autoexec macro?

3. I'm thinking of letting some computers use 2010 Access Runtime. i've never used it myself or had users use Runtime without having Access installed. does anyone know if there are issues?