Re-linking MS Access tables with VB.NET 2010
Re-linking MS Access tables with VB.NET 2010
Re-linking MS Access tables with VB.NET 2010
Re-linking MS Access tables with VB.NET 2010
Re-linking MS Access tables with VB.NET 2010
Re-linking MS Access tables with VB.NET 2010 Re-linking MS Access tables with VB.NET 2010 Re-linking MS Access tables with VB.NET 2010 Re-linking MS Access tables with VB.NET 2010 Re-linking MS Access tables with VB.NET 2010 Re-linking MS Access tables with VB.NET 2010 Re-linking MS Access tables with VB.NET 2010 Re-linking MS Access tables with VB.NET 2010
Re-linking MS Access tables with VB.NET 2010 Re-linking MS Access tables with VB.NET 2010
Re-linking MS Access tables with VB.NET 2010
Go Back  Xtreme Visual Basic Talk > > > Re-linking MS Access tables with VB.NET 2010


Reply
 
Thread Tools Display Modes
  #1  
Old 02-03-2013, 03:41 PM
CrystalBall CrystalBall is offline
Newcomer
 
Join Date: Jan 2013
Posts: 7
Default Re-linking MS Access tables with VB.NET 2010


I have a database split between data and queries, and need to be able to handle the files being moved, so re-link at startup.

VB6 let me just uses DAO and this didnt seem to work well in .NET (no errors, just fails to make any changes)

reseacrh suggested the following code...

Imports ADOX

Dim cat As Catalog = New Catalog()
Dim tbl As Table
Dim OracleConn As String = "PROVIDER = Microsoft.Jet.OLEDB.4.0;DATA SOURCE = " & App_Path() & "Fixtures 2013.MDB"

Try
cat.ActiveConnection = "PROVIDER = Microsoft.Jet.OLEDB.4.0;DATA SOURCE = " & App_Path() & "Fixtures_FE.MDB"

For Each tbl In cat.Tables
'Check to make sure the table is a linked table
If tbl.Type = "LINK" Then
tbl.Properties("Microsoft.Jet.OLEDB.4.0:Create Link").Value = True
tbl.Properties("Microsoft.Jet.OLEDB.4.0:Link Provider String").Value = OracleConn
tbl.Properties("Microsoft.Jet.OLEDB.4.0:Cache Link Name/Password").Value = True
End If
Next
Catch ex As Exception
MsgBox(ex.Message)
End Try

but this errors at cat.ActiveConnection with "Argument are of the wrong type, are out of acceptable range, or are in conflict with one another"

Project built without error, so can anyone offer a reason why thi fails, or suggest an alternative ?
Reply With Quote
  #2  
Old 02-04-2013, 08:31 AM
DrPunk's Avatar
DrPunkRe-linking MS Access tables with VB.NET 2010 DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

First question that springs to my mind is whether App_Path() is returning with a trailing backslash or not?
__________________
There are no computers in heaven!
Reply With Quote
  #3  
Old 02-04-2013, 02:29 PM
CrystalBall CrystalBall is offline
Newcomer
 
Join Date: Jan 2013
Posts: 7
Default

App_Path is ok, and I think I have a solution (posted below in case its of use, by my further grips is HOW DO YOU HANDLE FORMS CREATED WITH THE FORMS WIZARD - Have created a Project DataSource (and have a 2-tire access database). The forms were created by dragging a DataGridView to the form and tidying it up. Now - evn if I update the links, the project datasources seem to lose their link to the underlying database>

It is driving me mad, to the point that that perhaps this tool is not as useful as I hoped. I dont want to go back to VB6, but that handled this sort of stuff in a matter of seconds, and ALWAYS WORKED !!

H E L P !!

Code to re-link Access tables

Sub CreateLinkedExternalTable(ByVal sDB As String, ByVal sLinkDB As String, ByVal strLinkTbl As String, ByVal bMessages As Boolean)

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 = " & sDB)

ADOXCatalog.ActiveConnection = ADOConnection

ADOXTable.Name = strLinkTbl
ADOXCatalog.Tables.Delete(strLinkTbl)

ADOXTable.Name = strLinkTbl
ADOXTable.ParentCatalog = ADOXCatalog
ADOXTable.Properties("Jet OLEDB:Link Provider String").Value = "MS Access;DATABASE=" & sLinkDB
ADOXTable.Properties("Jet OLEDB:Remote Table Name").Value = strLinkTbl
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

If bMessages Then MsgBox(ADOXTable.Name & " has been successfully re-linked...")

End Sub
Reply With Quote
Reply

Tags
ado, vb.net 2010


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Re-linking MS Access tables with VB.NET 2010
Re-linking MS Access tables with VB.NET 2010
Re-linking MS Access tables with VB.NET 2010 Re-linking MS Access tables with VB.NET 2010
Re-linking MS Access tables with VB.NET 2010
Re-linking MS Access tables with VB.NET 2010
Re-linking MS Access tables with VB.NET 2010 Re-linking MS Access tables with VB.NET 2010 Re-linking MS Access tables with VB.NET 2010 Re-linking MS Access tables with VB.NET 2010 Re-linking MS Access tables with VB.NET 2010 Re-linking MS Access tables with VB.NET 2010 Re-linking MS Access tables with VB.NET 2010
Re-linking MS Access tables with VB.NET 2010
Re-linking MS Access tables with VB.NET 2010
 
Re-linking MS Access tables with VB.NET 2010
Re-linking MS Access tables with VB.NET 2010
 
-->