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.Ole Db
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(B yVal 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.Connectio n
'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.Conn ectionString = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=" & strDirFile
'OleDBConn.Open ()
ADOConnection.O pen("Provider=M icrosoft.Jet.OL EDB.4.0;Data Source=" &
strDirFile & ";Persist Security Info=False")
'Dim rst As New ADODB.Recordset
'rst = ADOConnection.E xecute("SELECT * FROM dbo_oas_company ")
'rst = ADOConnection.E xecute("SELECT * FROM dbo_T_Select")
'rst.MoveFirst( )
SyncLock (GetType(clsDB) )
'Try
cat.ActiveConne ction = ADOConnection
Try
'MessageBox.Sho w(cat.Tables.Co unt)
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.WriteLi ne(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).Co mmand
Try
'MsgBox(intX & " - " & cmd.CommandText )
'87 properties in het totaal!!!
If cmd.Properties( "Jet OLEDB:ODBC Pass-Through Statement").Val ue = 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.Pro perties("Jet OLEDB:Pass Through Query Connect String").Value)
'78
'cmd.Properties (87)
End If
Catch ex As System.Runtime. InteropServices .COMException
Console.WriteLi ne(ex.Message)
Catch ex As Exception
RaiseEvent Refreshing_Item ("Error Refreshing query: " & strDB & " - " &
strProcName)
End Try
'MsgBox(cmd.Com mandType())
'MsgBox(cmd.Pro perties("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.OpenDatabas e(strDirFile)
'ldb = New DAO.DBEngine
'ldb.Connect = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & strDirFile
'Dim r As Integer
'For r = 0 To ldb.QueryDefs.C ount - 1
'If (ldb.QueryDefs( r).Type = "eeer") And (Left(ldb.Query Defs(r).Connect ,
intLen) = strOld) Then
'End If
'Next
'Catch ex As Exception
'MessageBox.Sho w(ex.Message & ex.Source, "Exception - " & strDB)
'Finally
'End Try
End SyncLock
RaiseEvent Refresh_Ready(M e.strDB, intT, intQ)
cat.ActiveConne ction = Nothing
ADOConnection.C lose()
cat = Nothing
tbl = Nothing
cmd = Nothing
prc = Nothing
End Sub
End Class
"Oceania" <da****@datec.c om-dot-fj.no-spam.invalid> wrote in message
news:40******** @Usenet.com...
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....
Posted Via Usenet.com Premium Usenet Newsgroup Services
----------------------------------------------------------
** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
----------------------------------------------------------
http://www.usenet.com
