link table to just created table in backend 2

[WillemdeNie]

I have created a table using this code in the front-end:

On Error GoTo Err_ConversieTabellen

Dim conDatabase As ADODB.Connection
Dim sDBName As String
Set conDatabase = New ADODB.Connection
conDatabase.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Niebotel\NiebotelHaven.mdb;User Id=Bestuur;Password=XXXXXX;Jet OLEDB:System Database=C:\Program Files\Niebotel\SystemNiebotel.mdw;"

Database.Open
conDatabase.Execute "CREATE TABLE Gemeente(" & _
"Test INTEGER NOT NULL," & _
"FirstName Currency default 0)"
conDatabase.Close

The table is created (in the back-end)on moment of execution; so this OK
Now I would like to link the table.
How to do???
Help will be appriciated!

Willem

 

[Golom]

Like this?

Sub LinkJetTable( _
    TableName As String, _
    SourcePath As String, _
    DestConn As ADODB.Connection)
  
   Dim cat As New ADOX.Catalog
   Dim tbl As New ADOX.Table
   
   ' Open the catalog
   Set cat.ActiveConnection = DestConn 
   
   ' Set the name and target catalog for the table
   tbl.Name = TableName 
   Set tbl.ParentCatalog = cat
   
   ' Set the properties to create the link
   tbl.Properties("Jet OLEDB:Create Link") = True
   tbl.Properties("Jet OLEDB:Link Datasource") = SourcePath
   tbl.Properties("Jet OLEDB:Link Provider String") = ";Pwd=password"
   tbl.Properties("Jet OLEDB:Remote Table Name") = TableName
   
   ' Append the table to the collection
   cat.Tables.Append tbl
   
   Set cat = Nothing
   
End Sub

which you would call with

LinkJetTable "Gemeente", _
"C:\Program Files\Niebotel\SystemNiebotel.mdw", _
CurrentConn

 

[WillemdeNie]

Thanks for helping, Little problem left: I get a compilation error on the DIM statements; Userdefined type not defined (or something like that I have translated the message from Dutch).
Could it be that I have to include a specific library?
Willem

 

[RoyVidar]

Yes, Microsoft ADO Ext. 2.# for DDL and security.

Roy-Vidar

 

[WillemdeNie]

Hi again; already found the library (I think)

 

[WillemdeNie]

Hi Roy
Hope you do not mind:
Compilation error on:
LinkJetTable "Gemeente", _
"C:\Program Files\Niebotel\SystemNiebotel.mdw", _
CurrentConn
On statement CuttentConn (variable not defined)
Willem

 

[RoyVidar]

Golom probably means your current connection (conDatabase)

Roy-Vidar

 

[Golom]

Thanks Roy. Never post without testing I guess.

 

[WillemdeNie]

Ok Thanks
I am starting to understand
I have changed also the second variable into:C:\Program files etc in the name of the database in which is the table. In the example was referred to the workgroup. I assume this is right now

It runs fine upto the the last statment in the sub:
cat.Tables.Append tbl
Then a message appears:
Object Gemeente already exists

???

 

[Golom]

That means that you've already done it. You can't link the same table with the same name more than once.

 

[RoyVidar]

I'm just thinking, isn't the table alredy part of the collection? Does it need to be appended?

Roy-Vidar

 

[Golom]

I think that, from the front end, he's adding a table to the back end (original post.) Now he wants to link that new back end table to the front end ... at least that's what I was assuming when I responded.

 

[WillemdeNie]

Correct!
I do not understand what your question means; but I will try to give you some info:
The table has been created (running VBA inthe fromt-end) in the back-end .mdb.
I would like to use this table in the front-end .mdb and therefore normally I link the table.
Only now I have to do it from VBA
So your word "collection" and "appended" does not ring a bell with me. Can I check that somewhere? Or do you already know?
Willem

 

[RoyVidar]

This is my error - Goloms routine needs to be called with a connection to the destination database, perhaps currentproject.connection.

Roy-Vidar

 

[Golom]

A "collection" is a general term meaning (and this is really imprecise) a group of things like tables in a database or fields in a table that are contained in or associated with some "master" entity. "Database" or "Table" in these examples.

When you "append" something to a collection you simply add a new "thing" to the group (e.g add a new table or add a new field.)

When you created the new table in the back end you appended it to the back end's tables collection. Your objective now is to append the same table (as a linked table) to the front end's tables collection.

Does that clarify ... or further muddy ... the waters?

 

[WillemdeNie]

Yes your explanation is clear.
I indeeed would like to append the neew table to my collection of linked tables.

Untill now:
This what is in the example

LinkJetTable "Gemeente", _
"C:\Program Files\Niebotel\SystemNiebotel.mdw", _
CurrentConn


I changed it into:

conDatabase.Open
LinkJetTable "Gemeente", _
"C:\Program Files\Niebotel\NiebotelHaven.mdb", _
conDatabase
conDatabase.Close

In which the path is the backend in which the table has been created
It seems there are no errors until the statment:
cat.Tables.Append tbl
error says: object Gemeente already exists??
Although the link is not there
so not in my collection

 

[RoyVidar]

In the call to Goloms sub, replace conDatabase with a connection to the current database, for instance currentproject.connection.

Roy-Vidar

 

[Golom]

Change conDatabase in the call to LinkJetTable to currentproject.connection


conDatabase is the connection to the back end where the table already exists.

currentproject.connection (Thanks Roy) is the connection to the front end where you want the link created.

 

[WillemdeNie]

Guys, Great help; it works!
Greetings Willem from Holland