relink access tables using vb.net coding.... Help

  • Thread starter Thread starter Oceania
  • Start date Start date
O

Oceania

Hi All,

I did try using the sample code from the ADO.net forum to link one
table, it is working fine. But, I got a problem when I tried to link
all tables (20 tables)... Please help, thank you....

Rgds....
 
Hi Oceania,

Maybe it is better what you want to archieve because that ADOX code you are
using is for me real not understandable anymore. (But as I see it, I think
it is incomplete, I see not any recordset or something other to get the data
in it).

So tell us what you try to do.

And I think there are not much people who want to learn that again.

Cor
 
Hi,

Some time ago I tryed the same thing. I had to refresh a whole bunch of
query's and table's in a whole bunch of Access-databases. I looked some days
on the internet for solutions, tryed really a lot, but nothing worked really
fine. I had some solution that for some reason didn't work with every
table/query, and I never foudn out why. I ended up doing this in Access,
hehe.

Maybe you can do something with my VB.NET code? Maybe you can find usefull
things in it or improve it?

If you findsomething that really works, can you post it please?

Pieter




Option Explicit On

Imports System.Runtime.InteropServices

Imports System.Data.OleDb

Public Class clsDB

Public strPath As String

Public strDB As String

Public intLen As Integer

Public strConn As String

Public strOld As String

Public strDirFile As String

Public Event Refresh_Ready(ByVal strDB As String, ByVal intTa As Integer,
ByVal intQu As Integer)

Public Event Refreshing_Item(ByVal strText As String)

Public Sub subRefresh()

'On Error Resume Next

Dim ADOConnection As New ADODB.Connection

'Dim OleDBConn As New OleDbconnection

Dim cat As New ADOX.Catalog

Dim tbl As New ADOX.Table

Dim prc As ADOX.Procedure

Dim strProcName As String

Dim cmd As ADODB.Command

Dim intT, intQ As Integer

intT = 0

intQ = 0

'OleDBConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & strDirFile

'OleDBConn.Open()

ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
strDirFile & ";Persist Security Info=False")

'Dim rst As New ADODB.Recordset

'rst = ADOConnection.Execute("SELECT * FROM dbo_oas_company")

'rst = ADOConnection.Execute("SELECT * FROM dbo_T_Select")

'rst.MoveFirst()

SyncLock (GetType(clsDB))

'Try



cat.ActiveConnection = ADOConnection

Try

'MessageBox.Show(cat.Tables.Count)

For Each tbl In cat.Tables

Try

If tbl.Type = "PASS-THROUGH" Then

If UCase(Left(tbl.Properties("Jet OLEDB:Link Provider String").Value(),
intLen)) = UCase(strOld) Then

RaiseEvent Refreshing_Item("Refreshing table: " & strDB & " - " & tbl.Name)

intT = intT + 1

tbl.Properties("Jet OLEDB:Link Provider String").Value() = strConn

End If

End If

Catch ex As Exception

RaiseEvent Refreshing_Item("Error Refreshing table: " & strDB & " - " &
tbl.Name)

End Try

Next

Catch ex As System.Runtime.InteropServices.COMException

Console.WriteLine(ex.Message)

Catch ex As Exception

RaiseEvent Refreshing_Item("Error Refreshing tables: " & strDB)

End Try

Try

For Each prc In cat.Procedures

strProcName = prc.Name

cmd = New ADODB.Command

cmd = cat.Procedures(strProcName).Command

Try

'MsgBox(intX & " - " & cmd.CommandText)

'87 properties in het totaal!!!

If cmd.Properties("Jet OLEDB:ODBC Pass-Through Statement").Value = True Then
'79

If UCase(Left(cmd.Properties("Jet OLEDB:Pass Through Query Connect
String").Value, intLen + 5)) = UCase("ODBC;" & strOld) Then

RaiseEvent Refreshing_Item("Refreshing query: " & strDB & " - " &
strProcName)

intQ = intQ + 1

cmd.Properties("Jet OLEDB:Pass Through Query Connect String").Value = "ODBC;
" & strConn

End If

'MsgBox(cmd.Properties("Jet OLEDB:Pass Through Query Connect String").Value)

'78

'cmd.Properties(87)

End If

Catch ex As System.Runtime.InteropServices.COMException

Console.WriteLine(ex.Message)

Catch ex As Exception

RaiseEvent Refreshing_Item("Error Refreshing query: " & strDB & " - " &
strProcName)

End Try

'MsgBox(cmd.CommandType())

'MsgBox(cmd.Properties("Jet OLEDB:Link Provider String").Value())

Next

Catch ex As Exception

RaiseEvent Refreshing_Item("Error Refreshing query's: " & strDB)

End Try

'Dim wrk As DAO.Workspace

'Dim ldb As DAO.Database

'Dim tdf As DAO.TableDef

'Dim qdf As DAO.QueryDef

'wrk = New DAO.DBEngine

'ldb = wrk.OpenDatabase(strDirFile)

'ldb = New DAO.DBEngine

'ldb.Connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDirFile

'Dim r As Integer

'For r = 0 To ldb.QueryDefs.Count - 1

'If (ldb.QueryDefs(r).Type = "eeer") And (Left(ldb.QueryDefs(r).Connect,
intLen) = strOld) Then

'End If

'Next

'Catch ex As Exception

'MessageBox.Show(ex.Message & ex.Source, "Exception - " & strDB)

'Finally

'End Try

End SyncLock

RaiseEvent Refresh_Ready(Me.strDB, intT, intQ)

cat.ActiveConnection = Nothing

ADOConnection.Close()

cat = Nothing

tbl = Nothing

cmd = Nothing

prc = Nothing

End Sub

End Class









Oceania said:
Hi All,

I did try using the sample code from the ADO.net forum to link one
table, it is working fine. But, I got a problem when I tried to link
all tables (20 tables)... Please help, thank you....

Rgds....
Click to expand...
 
Hi Cor,

This is what I really try to do:

I always have a front-end empty database namely dbtest, and I also
have a permanent back-end namely dbbend what contains about 20
tables. And, everytime the users open the application, it will create
the link from dbtest to dbbend. Then, these linked will drop when the
user logged out the application. Hope it make sense... Thank you..
Rgds,
 
Hi Cor,

This is what I really try to do:

I always have a front-end empty database namely dbtest, and I also
have a permanent back-end namely dbbend what contains about 20
tables. And, everytime the users open the application, it will create
the link from dbtest to dbbend. Then, these linked will drop when the
user logged out the application. Hope it make sense... Thank you..
Rgds,
 
Back
Top