Manage ODBC

[alex]

Manage ODBC

Hello,
Using Access ’03…

I use Access and ODBC to connect to a variety of data sources, such as
Oracle and SQL Server.

Sometimes I need to modify a preexisting connection (change password,
re-link table, etc.). I don’t have administrative privileges to my
computer(s) (cannot access control panel) and have to modify my
connections manually; e.g., New > Link Table > ODBC Databases ()…

I assumed that there was a file somewhere that held the connection
information and that information could be modified within the file.
I’ve been unable to find such a file, however.

I’m wondering if anyone knows how to modify an ODBC connection without
using the wizard on the control panel or having to “reestablish” a
connection; i.e., does the information exist in a file that can be
modified?

Thanks,
alex

 

[John Spencer]

Each linked table has a connect property. That is where the connection is
defined.

Currentdb.TableDefs("NameofTable").Connect
will show you the path

You can set the connect path with VBA using something like the following to
re-connect to an Access (JET/ACE) database.
Currentdb.TableDefs("NameofTable").Connect =
";DATABASE=K:\Info Sys\spencer\PRS Work\My BackEnd MAY 2010.mdb"

For an MS SQL 2005 database the connect string might look more like the
following.
ODBC;DRIVER=sql server;SERVER=NameOfServer;APP=Microsoft Office
2003;DATABASE=AIDS;Trusted_Connection=Yes

Manage ODBC

Hello,
Using Access ’03…

I use Access and ODBC to connect to a variety of data sources, such as
Oracle and SQL Server.

Sometimes I need to modify a preexisting connection (change password,
re-link table, etc.). I don’t have administrative privileges to my
computer(s) (cannot access control panel) and have to modify my
connections manually; e.g., New > Link Table > ODBC Databases ()…

I assumed that there was a file somewhere that held the connection
information and that information could be modified within the file.
I’ve been unable to find such a file, however.

I’m wondering if anyone knows how to modify an ODBC connection without
using the wizard on the control panel or having to “reestablish” a
connection; i.e., does the information exist in a file that can be
modified?

Thanks,
alex

--

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[alex]

Each linked table has a connect property.  That is where the connectionis
defined.

Currentdb.TableDefs("NameofTable").Connect
will show you the path

You can set the connect path with VBA using something like the following to
re-connect to an Access (JET/ACE) database.
   Currentdb.TableDefs("NameofTable").Connect =
   ";DATABASE=K:\Info Sys\spencer\PRS Work\My BackEnd MAY 2010.mdb"

For an MS SQL 2005 database the connect string might look more like the
following.
   ODBC;DRIVER=sql server;SERVER=NameOfServer;APP=Microsoft Office
2003;DATABASE=AIDS;Trusted_Connection=Yes












--

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County- Hide quoted text -

- Show quoted text -

Thanks John. I had no idea you could do that.
alex