MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

automate odbc connection setup

  Asked By: Marty    Date: Mar 03    Category: MS Office    Views: 2613

We have a production database and copys of the production that are used
for analysis and reporting (avoids impacting production work).

Each day a new reporting database copy is made and one is deleted. As
a result I find that I constantly have to go into administrative tools
to point my odbc connections to an existing database (usually the name
has the date in it so we can tell which copy we are using).

I'd like to automate some of my reporting processes but having to redo
my odbc connections each day is a problem I have not solved.

Is there a way of establishing odbc connections in code without using
admin tools?



4 Answers Found

Answer #1    Answered By: Viheke Fischer     Answered On: Mar 03

When you make changes in Admin Tools / ODBC, the changes go into the
Registry (unless you make a File DSN, but that's another story - actually -
that's real handy too because it shows you the settings in a text file
format for a certain connection).

For User DSN's check out HKCU\Software\ODBC\ODBC.INI.

For System DSN's, check out HKLM\Software\ODBC\ODBC.INI

Answer #2    Answered By: Jeanette Greene     Answered On: Mar 03

I think I found it

look up odbcconf.exe.

Has anyone used it?

Answer #3    Answered By: Isabella Campbell     Answered On: Mar 03

I just ran it from a command window and it worked

C:\>odbcconf /a {CONFIGSYSDSN "SQL
Server" "DSN=test|Database=database20070522|server=sql1|Trusted_Connecti

Answer #4    Answered By: Logan Bouchard     Answered On: Mar 03

Do some research on DSN less ODBC. You can create the connection  in VBA
even though the connection is not in Admin. You will need to make sure the
user has a valid userid and pwd to the system or it will fail. You could
hard code  yours or a generic User and PWD but it's not secure. Some of the
code on these links shows you how to embed user/pwd, I prefer not to. I
know for a fact if you omit user/pwd on the AS400 string, it will prompt for

Link to Google Search

IBM> site shows how to create one with many different data sources. I use
AS400 & MSAccess frequently.

Didn't find what you were looking for? Find more on automate odbc connection setup Or get search suggestion and latest updates.