How to connect an ODBC data source through code?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Guys, I'm looking to try to connect a table through a connection string in VBA.

Can you please point me at the function I should be using please, or drop me
a line of an example connection string.

Thanks in advance.
 
Dave.

DaveO said:
Guys, I'm looking to try to connect a table through a connection
string in VBA.
Can you please point me at the function I should be using please,
or drop me a line of an example connection string.
Click to expand...

Here's, how I do it:
First, create a system dsn ("MyDataSource") that points to the database.
(Start, Control Panel, Administrative Tools, Datasources (ODBC))

To create a linked table using VBA:
Create a new TableDef object, set the Connect and SourceTableName properties
and append it to the TableDefs collection:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef

Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef("MyLinkedTable")

With tdf
.Connect = "ODBC;DSN=MyDataSource"
.SourceTableName = "NameOfSourceTable"
End With
dbs.TableDefs.Append tdf

Set tdf = Nothing
Set dbs = Nothing
 
Thanks Wolfgang, the DSN and everything is all set up and I already use it.

Quick question.

If I wish to dynamically name the table, based the actual table name, can I
rename this once I've picked the table up?

Example.

DSN has Tables 1 2 and 3.

I want to link them through as tables 1 2 and 3, but by statically declaring
their name how do I rename it before the ...

dbs.TableDefs.Append tdf

Thanks again!

Dave.
 
Hello Dave.

You can specify the desired name in the CreateTableDef method.
Or, before appending, specify the name property:

.Name = "NewName"
 
Thanks for the code.

Have managed to get myself to here ...

Code: 
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim tdftest As DAO.TableDef
Dim strTblName As String

Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef("MyLinkedTable")

With tdf
.Connect = "ODBC;DSN=Stack1 Remote Tables; UID=****; PWD=****"

For Each tdftest In tbf.SourceTableName

strTblName = tdf.SourceTableName

Next tdftest

End With

tdf.Name = strTblName

dbs.TableDefs.Append tdf

Set tdf = Nothing
Set dbs = Nothing

But am having problems. It says I require an object. Now I've tried to use
tdftest.sourcetablename but it says I need to assign it to a variable, but if
I do that it throws another error.

In short I'm a bit lost with this and don't have a clue as to where to look.

Any help would be greatly appreciated.

Thanks.
 
Dave.

DaveO" said:
Code: 
 [...]
For Each tdftest In tbf.SourceTableName
strTblName = tdf.SourceTableName
Next tdftest[/QUOTE]

Whoops, SourceTableName is not a collection or array. Youe must specify a
name for that property, like
.SourceTableName = "NameOfSourceTable"
This is the name of the table in the ODBC database.
Lets say, the table is named TableOne and you want a linked table named One
pointing to the TableOne table:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef("One")
With tdf
.Connect = "ODBC;DSN=Stack1 Remote Tables; UID=****; PWD=****"
.SourceTableName = "dbo.TableOne"
End With
dbs.TableDefs.Append tdf
Set tdf = Nothing
Set dbs = Nothing
Click to expand...
 
Wolfgang, perhaps I haven't explained myself very well.

The ODBC db holds multiple tables, each with different names. These names
change on a monthy basis and I want to automate attaching them, rather than
have to use the import wizrd.

So I don't know the name of the tables, but the last 4 characters will
always end '0805'. So how would I attach all tables, with different names to
the one db in code?

Thanks for the help so far!!!

Wolfgang Kais said:
Dave.

DaveO" said:
Code: 
 [...]
For Each tdftest In tbf.SourceTableName
strTblName = tdf.SourceTableName
Next tdftest[/QUOTE]

Whoops, SourceTableName is not a collection or array. Youe must specify a
name for that property, like
.SourceTableName = "NameOfSourceTable"
This is the name of the table in the ODBC database.
Lets say, the table is named TableOne and you want a linked table named One
pointing to the TableOne table:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef("One")
With tdf
.Connect = "ODBC;DSN=Stack1 Remote Tables; UID=****; PWD=****"
.SourceTableName = "dbo.TableOne"
End With
dbs.TableDefs.Append tdf
Set tdf = Nothing
Set dbs = Nothing
[/QUOTE]
Click to expand...
Click to expand...
 
Dave.

DaveO said:
Wolfgang, perhaps I haven't explained myself very well.

The ODBC db holds multiple tables, each with different names. These names
change on a monthy basis and I want to automate attaching them, rather
than
have to use the import wizrd.

So I don't know the name of the tables, but the last 4 characters will
always end '0805'. So how would I attach all tables, with different names
to
the one db in code?

Thanks for the help so far!!!
Click to expand...

I think that you should know the names of the tables. And the "0805".
You could store the prefixes of the table names in a local table and loop
through these names to attach the tables.
 
Hello

But even if the definition of the CreateTableDef.Connection via ODBC
has been done correctly, I still have to push the OK button to really
set up the connection. The alternative is e.g. to make it a trusted
connection (when connnecting to SQL Server). But does there exist a
indicator/parameter/other to avoid making it trusted and still not to
have to push that freaking OK button?

Thanks.

Philippe.

DaveO schreef:
 
Back
Top