OLE DB Connections

R

Rob Diamant

I know how to create an OLEDB connection in VBA, but is there a way to
establish a connection and have it display in the Database Window or am I
stuck with ODBC.

TIA

Rob
 
M

Mike Labosh

I know how to create an OLEDB connection in VBA, but is there a way to
establish a connection and have it display in the Database Window or am I
stuck with ODBC.
Click to expand...

I think you're stuck with ODBC. But I'm more interested in finding out why
you want a Linked Table via OLEDB. Have you run into an ODBC-related
problem?
--
Peace & happy computing,

Mike Labosh, MCSD

"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS!"
~~ General Barringer ~~
 
R

Rob Diamant

Mike,

I have 200+ SQL Server client databases that I need to connect to at
different times. What I would like to do is create a program that
automatically connects to the server/database and builds the necessary
links. Also I heavily use Crystal Reporst (using OLEDB) and would like to
keep data connections consistant.

Thanks
Rob
 
M

Mike Labosh

I have 200+ SQL Server client databases that I need to connect to at
different times. What I would like to do is create a program that
automatically connects to the server/database and builds the necessary
links. Also I heavily use Crystal Reporst (using OLEDB) and would like to
keep data connections consistant.
Click to expand...

The trouble is that the only [that I know of] way for Access to have a
"Linked Table" to SQL Server is via ODBC. It's also probably a pain to have
to go around to user machines to setup the DSN!

What I would do is use some VBA code to get an ADO Recordset on something
like this:

SELECT Table_Name FROM Information_Schema.Tables

Then you can loop over the records and for each name, you could create a
DAO.TableDef object of the name and set it's connection(?) property to your
ODBC Connect String and call its RefreshLink(?) method. Don't forget to
finally TableDefs.Append() your DAO.TableDefs, or they will have been
constructed and then disappear into thin air :)
--
Peace & happy computing,

Mike Labosh, MCSD

"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS!"
~~ General Barringer ~~
 
R

Rob Diamant

Thanks, I'll give it a try.

Rob
Mike Labosh said:
I have 200+ SQL Server client databases that I need to connect to at
different times. What I would like to do is create a program that
automatically connects to the server/database and builds the necessary
links. Also I heavily use Crystal Reporst (using OLEDB) and would like to
keep data connections consistant.
Click to expand...

The trouble is that the only [that I know of] way for Access to have a
"Linked Table" to SQL Server is via ODBC. It's also probably a pain to
have
to go around to user machines to setup the DSN!

What I would do is use some VBA code to get an ADO Recordset on something
like this:

SELECT Table_Name FROM Information_Schema.Tables

Then you can loop over the records and for each name, you could create a
DAO.TableDef object of the name and set it's connection(?) property to
your
ODBC Connect String and call its RefreshLink(?) method. Don't forget to
finally TableDefs.Append() your DAO.TableDefs, or they will have been
constructed and then disappear into thin air :)
--
Peace & happy computing,

Mike Labosh, MCSD

"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS!"
~~ General Barringer ~~
Click to expand...
 
Top