Topics Search

Create and Setup an ODBC Database Connection

Create and Setup an ODBC Database Connection
Views: 205
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:

Similar posts...


ODBC-Connection failed to sublist

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

bypass odbc connection

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

Creating an ODBC Connection in VBA

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

Access Runtime and ODBC connection can't get it to work..

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

Access 2003 ODBC Question

Access 2003 ODBC Question Icon
I am supporting a legacy Access 2003 application with ODBC connections to several Oracle tables. My customer is upgrading to Oracle 11g which does not support ODBC. Is there any way I can create a connection string or otherwise link to an external Oracle table without ODBC?

embedded odbc string in table?

embedded odbc string in table? Icon
Just wondering if it is possible to somehow embed the ODBC connection string into a MS Access table so as to link the table to SQL Server without relying on setting up a ODBC connection on that machine

Connecting to mySQL database, or rather not connecting!

Connecting to mySQL database, or rather not connecting! Icon
I'm trying to connect to a remote mySQL database.

I created a passthrough query and set the ODBC connection string as follows (using generic names here):

ODBC;Driver={MySQL ODBC 5.1 Driver};;Database=mydatabase ;User=myusername;Password=myPassword;Option=3

I get an "ODBC error - call failed" message. If I put in what I know is an incorrect password, I get the mySQL connection screen. I can put in the correct password and hit "Test" and it says "Connected".

There is even a dropdown with all the databases on the server. So I know I'm connecting, but my query still fails. I don't get it!

Source ConnectStr Property and ODBC

Source ConnectStr Property and ODBC Icon
The best way to learn and understand more about the Connection String Syntax of different ODBC Data Sources is to go through the following steps and look at the Connection String of the Linked Table:

Create a DSN connection

Create a DSN connection Icon
At this point you need to create a data link. When you execute an ASP file, Windows must know which database you're connecting to. An ODBC Data source Name (DSN) simply stores the information(the name, the path, the user, and the password) required to connect to a specific database. OLE DB connections are superior to DSN because they're quicker to connect, but you must include the specific connection string in the script, and that's beyond the scope of this article. A DSN connection requires little know-how to set up. There are two types of DSN.

ODBC Connection through Access VBA using Windows 7

ODBC Connection through Access VBA using Windows 7 Icon
We have a push to begin rolling out machines with Windows 7. In our testing of our Operations Access Databases, we are running into some real weird things going on when we're trying to establish an ODBC connection through Access VBA into Oracle Databases.

Is there a "Red Book" on anything regarding ODBC connections using Windows 7?