relink access tables.... Help

O

Oceania

Hi

I tried out some sample code to create link one table which worked
fine, but not all tables (about 20 tables to link).
Rgds....

here comes the sample code from this forum:
Sub CreateAttachedAccessTableWithADOX()

Dim ADOXTable As New ADOX.Table
Dim ADOXCatalog As New ADOX.Catalog
Dim ADOConnection As New ADODB.Connection

Try

ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db1.mdb;" & _
"Jet OLEDB:Engine Type=4;")

ADOXCatalog.ActiveConnection = ADOConnection

ADOXTable.Name = "Table11Linked"
ADOXTable.ParentCatalog = ADOXCatalog
ADOXTable.Properties("Jet OLEDB:Link Provider String").Value = "MS
Access;DATABASE=E:\My
Documents\AccessDB.mdb"
ADOXTable.Properties("Jet OLEDB:Remote Table Name").Value = "Table11"

ADOXTable.Properties("Jet OLEDB:Create Link").Value = True
ADOXCatalog.Tables.Append(ADOXTable)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
ADOConnection.Close()
End Try

End Sub
 
O

Oceania

Hi Cor,

No special reason at all, if helping codes in ADO.Net it's just
fine......

Rgds,
 
P

Paul Clement

On 20 Feb 2004 18:27:29 -0600, (e-mail address removed)-dot-fj.no-spam.invalid (Oceania) wrote:

¤ Hi
¤
¤ I tried out some sample code to create link one table which worked
¤ fine, but not all tables (about 20 tables to link).
¤ Rgds....
¤
¤ here comes the sample code from this forum:
¤ Sub CreateAttachedAccessTableWithADOX()
¤
¤ Dim ADOXTable As New ADOX.Table
¤ Dim ADOXCatalog As New ADOX.Catalog
¤ Dim ADOConnection As New ADODB.Connection
¤
¤ Try
¤
¤ ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
¤ "Data Source=e:\My Documents\db1.mdb;" & _
¤ "Jet OLEDB:Engine Type=4;")
¤
¤ ADOXCatalog.ActiveConnection = ADOConnection
¤
¤ ADOXTable.Name = "Table11Linked"
¤ ADOXTable.ParentCatalog = ADOXCatalog
¤ ADOXTable.Properties("Jet OLEDB:Link Provider String").Value = "MS
¤ Access;DATABASE=E:\My
¤ Documents\AccessDB.mdb"
¤ ADOXTable.Properties("Jet OLEDB:Remote Table Name").Value = "Table11"
¤
¤ ADOXTable.Properties("Jet OLEDB:Create Link").Value = True
¤ ADOXCatalog.Tables.Append(ADOXTable)
¤ Catch ex As Exception
¤ MessageBox.Show(ex.Message)
¤ Finally
¤ ADOConnection.Close()
¤ End Try
¤
¤ End Sub
¤

Are you saying that some tables are linking but others are not? Are you receiving any error
messages? From what type of database (Access? SQL Server?) are you attempting to link tables to?


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
P

Paul Clement

¤ Hi Oceannia,
¤
¤ Is there a special reason that you use classic Ado instead of AdoNet?

ADO.NET does not have native support for linking tables to an Access database.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
C

Cor

Hi Paul,

Before today I did even not know that it exist.
(I tried to read something about it on MSDN)

Can you give me an example when I should use this instead of Adonet
(This is serious, I also use ADOX to create a database because that is not
possible in Adonet)

Cor
 
O

Oceania

Hi Paul,

It is linking the first table (MS Access 2000) successful, then given
me the error message:
"Operation is not allow in this context"

Rgds,

Here comes the code I tried from this forum......

Try
'Process/Tasks are Done Here.
CnnRL.Open()
CnnADO.Open()
'
catADOXSource.ActiveConnection = CnnADO
catADOX.ActiveConnection = CnnRL
' Refreshing - Linked Tables - this one is working
fine.....
' For Each tblLink In catADOX.Tables
' If tblLink.Type = "LINK" Then
' tblLink.Properties("Jet OLEDB:Link
DataSource").Value = "C:\datecapp\transfer\dbbend.mdb"
' tblLink.Properties("Jet OLEDB:Create
Link").Value = True
' End If
' Next
' catADOX = Nothing
'
For Each tblSource In catADOXSource.Tables
With tblLink
' Name the new Table and set its ParentCatalog
property to the
' open Catalog to allow access to the Properties
collection.
.Name = tblSource.Name
.ParentCatalog = catADOX
' Set the properties to create the link.
.Properties("Jet OLEDB:Link DataSource").Value =
strProviderSource
.Properties("Jet OLEDB:Link Provider
String").Value = strProviderPW
.Properties("Jet OLEDB:Remote Table Name").Value =
tblSource.Name
.Properties("Jet OLEDB:Create Link").Value = True
End With
' Append the table to the Tables collection.
catADOX.Tables.Append(tblLink)
Next
catADOX = Nothing
catADOXSource = Nothing
Catch ex As Exception
MsgBox(ex.Message)
Finally
' Close the connection whather it happens.
CnnRL.Close()
CnnADO.Close()
End Try
 
P

Paul Clement

¤ Hi Paul,
¤
¤ Before today I did even not know that it exist.
¤ (I tried to read something about it on MSDN)
¤
¤ Can you give me an example when I should use this instead of Adonet
¤ (This is serious, I also use ADOX to create a database because that is not
¤ possible in Adonet)

You would use ADOX (or DAO) for features that tend to be database specific and of course unsupported
by ADO.NET or the DDL language of the database. For example, linking tables, so that they look just
like native Access tables, is somewhat specific to Microsoft Access.

There are a number of features in Microsoft Access that are not supported natively via ADO.NET.
Microsoft has been moving away from this database format (slowly) for some time now, placing more
emphasis on MSDE. As a result, they've left some of the supported functionality behind, which
requires that you resort to the COM-based functionality via interop.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
P

Paul Clement

On 23 Feb 2004 21:28:05 -0600, (e-mail address removed)-dot-fj.no-spam.invalid (Oceania) wrote:

¤ Hi Paul,
¤
¤ It is linking the first table (MS Access 2000) successful, then given
¤ me the error message:
¤ "Operation is not allow in this context"
¤
¤ Rgds,
¤
¤ Here comes the code I tried from this forum......
¤
¤ Try
¤ 'Process/Tasks are Done Here.
¤ CnnRL.Open()
¤ CnnADO.Open()
¤ '
¤ catADOXSource.ActiveConnection = CnnADO
¤ catADOX.ActiveConnection = CnnRL
¤ ' Refreshing - Linked Tables - this one is working
¤ fine.....
¤ ' For Each tblLink In catADOX.Tables
¤ ' If tblLink.Type = "LINK" Then
¤ ' tblLink.Properties("Jet OLEDB:Link
¤ DataSource").Value = "C:\datecapp\transfer\dbbend.mdb"
¤ ' tblLink.Properties("Jet OLEDB:Create
¤ Link").Value = True
¤ ' End If
¤ ' Next
¤ ' catADOX = Nothing
¤ '
¤ For Each tblSource In catADOXSource.Tables
¤ With tblLink
¤ ' Name the new Table and set its ParentCatalog
¤ property to the
¤ ' open Catalog to allow access to the Properties
¤ collection.
¤ .Name = tblSource.Name
¤ .ParentCatalog = catADOX
¤ ' Set the properties to create the link.
¤ .Properties("Jet OLEDB:Link DataSource").Value =
¤ strProviderSource
¤ .Properties("Jet OLEDB:Link Provider
¤ String").Value = strProviderPW
¤ .Properties("Jet OLEDB:Remote Table Name").Value =
¤ tblSource.Name
¤ .Properties("Jet OLEDB:Create Link").Value = True
¤ End With
¤ ' Append the table to the Tables collection.
¤ catADOX.Tables.Append(tblLink)
¤ Next
¤ catADOX = Nothing
¤ catADOXSource = Nothing
¤ Catch ex As Exception
¤ MsgBox(ex.Message)
¤ Finally
¤ ' Close the connection whather it happens.
¤ CnnRL.Close()
¤ CnnADO.Close()
¤ End Try
¤

Maybe you could identify on what line of code the error is occurring? I'm assuming that catADOX
still has a connection, although in your commented code you set it to Nothing. You should also be
creating a new instance of tblLink each time you add a new linked table - I don't see that in your
code.

I will also assume that the values of strProviderSource and strProviderPW are correct.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
O

Oceania

Hi Paul

It is given the error on this line
.ParentCatalog = catADOX
When it tried to link the second table

Please give an example as you mentioned:
"You should also be creating a new instance of tblLink each time you
add a new linked table - I don't see that in your code."

Rgds & Thank....
 
C

Cor

Hi Paul,

Thanks, I do/did not know that link function, and as you said it Microsoft
is leaving it, so probably I could not find much documentation more on MSDN.

Can you give me an example where I should use it?

Cor
 
P

Paul Clement

On 24 Feb 2004 22:37:58 -0600, (e-mail address removed)-dot-fj.no-spam.invalid (Oceania) wrote:

¤ Hi Paul
¤
¤ It is given the error on this line
¤ ParentCatalog = catADOX
¤ When it tried to link the second table
¤
¤ Please give an example as you mentioned:
¤ "You should also be creating a new instance of tblLink each time you
¤ add a new linked table - I don't see that in your code."
¤

Since you don't want to reuse the tblLink object you just created and appended to the Tables
collection, create a new instance for each new table:

For Each tblSource In catADOXSource.Tables
tblLink = New ADOX.Table
With tblLink
'...
'...


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
P

Paul Clement

¤ Hi Paul,
¤
¤ Thanks, I do/did not know that link function, and as you said it Microsoft
¤ is leaving it, so probably I could not find much documentation more on MSDN.
¤
¤ Can you give me an example where I should use it?

First, I'm assuming that you know what a linked Access table is. If so, then you would use it when
you need to add new linked tables to an Access database or when the location or information of the
linked table source has changed.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
O

Oceania

Thank you, Paul.....

It's working like magic, you're a wizardism....

Next is please give me a function to delete a file in the VB. net

Rgds,
 
P

Paul Clement

On 25 Feb 2004 20:39:09 -0600, (e-mail address removed)-dot-fj.no-spam.invalid (Oceania) wrote:

¤ Thank you, Paul.....
¤
¤ It's working like magic, you're a wizardism....
¤
¤ Next is please give me a function to delete a file in the VB. net
¤

System.IO.File.Delete


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
O

Oceania

Thank you, Paul....

If I want to find out the Access table property such as hidden objects
or system objects, please give me function. Thank you......

Rgds,
 
P

Paul Clement

On 26 Feb 2004 19:20:35 -0600, (e-mail address removed)-dot-fj.no-spam.invalid (Oceania) wrote:

¤ Thank you, Paul....
¤
¤ If I want to find out the Access table property such as hidden objects
¤ or system objects, please give me function. Thank you......
¤

See if the following code helps:

Public Sub ListAccessTablesADOX()

Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim prop As ADOX.Property

cnn.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db10.mdb;" & _
"Jet OLEDB:Engine Type=4;")

cat.ActiveConnection = cnn

For Each tbl In cat.Tables

Console.WriteLine(tbl.Name)
Console.WriteLine(tbl.Type)
Console.WriteLine(tbl.DateCreated)
For Each prop In tbl.Properties
Console.WriteLine(prop.Name & ":" & prop.Value)
Next prop

Next tbl

cnn.Close()

System.Runtime.InteropServices.Marshal.ReleaseComObject(cat)
System.Runtime.InteropServices.Marshal.ReleaseComObject(cnn)

cat = Nothing
cnn = Nothing

End Sub


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Change ODBC Link Provider String in Access DB using ADOX in VB.NET 2
creating linked tables using ADOX and C# 4
How to Link tables in ADO.NET 4
Access Linked table creation 1
Create Linked Table to AS/400 6
ADO Error Attempting to Link Table 1
How to, using Excel VBA, link a text file to a .mdb file? 0
Create Linked table in Access 3

Top