"Scott Tilton" <st*****@avantekservices.com> wrote in message
news:vo************@corp.supernews.com...
Thank you for your reply. The only start that I have is trying to adapt
the linking and refreshing of linked tables found in the sample databases
provided by Microsoft. However, since this does not address the issues I
am working with, I have been unable to do it. That is why I am writing here.
The basic strategy that I want to do is this:
1) Database Opens
2) Switchboard is set to run on startup. In the switchbaord is where I am
running the code to check the linked tables.
3) Open the INI file and retrieve the table information. Below is my
current INI settings:
------------------------------------------------
[Excel Data]
Spreadsheet Path=L:\Client Holding\Ideacomm\SrvCall2\
Spreadsheet File=IdeacomPartsPricing.xls
[GoldMine Data]
Contact1 Path=N:\Client GoldMines\Ideacom_GoldMine_4\Common\
Contact2 Path=N:\Client GoldMines\Ideacom_GoldMine_4\Common\
[Access Data]
Backend Database Path=L:\Client Holding\Ideacomm\SrvCall2\
Backend Database File=ServCall2_Data.mdb
[License]
License Name=Ideacomm
----------------------------------------------
I have no trouble getting this information. I bring each one of these
into a variable within Access.
4) Now, what I want to be able to do, which I am having trouble is taking
those variable and updating the specific linked table that each of those
go with. I just need to know how to address a specific linked table and
update it to that coressponding variable I pulled from the INI.
I do not even need it to check to see if the linked table works... I only
need it to see if it matches the variable, and if not update it to the
variable. Do you see what I am trying to do? I dont' think this is very
hard, I just have no experience communicating with the linked tables in
VBA.
I don't know how to do this. I have never done it before. Otherwise I
would be more than happy to show you my code.
Thanks,
Scott
Hi Scott
******Before you read any further******
You will need to amend the GetArray() function yourself.
It should all work, but I wouldn't ever run code from a stranger without
checking it through myself and then taking a proper backup first.
*******************************
There are a number of basic approaches to this problem and these often
involve looping through all of the linked tables and checking them and
refreshing them if necessary. This approach is used by Microsoft in the
demo Northwind database, howeverit is not without its drawbacks. If a
linked table is accidentally deleted, a new table is not re-created. Also
it is often more efficient to simply delete the tables and re-create them
rather than trying to refresh.
The following approach builds up a list of linked tables which *should* be
there. It then checks if the necessary external data sources are there and
if so, the code tries to delete the list of tables - though only if they are
linked - you wouldn't want to accidentally delete a local (real) table,
would you? It then re-creates the linked tables.
You can simply paste it all into a new module and from your startup
procedure (autoexec) just call CheckLinks().
Option Compare Database
Option Explicit
Private Function GetArray()
Dim astr(4, 3) As String
astr(0, 0) = "tblTest1"
astr(0, 1) = "C:\Shared\Book1.xls"
astr(0, 2) = "Sheet1$"
astr(1, 0) = "tblTest2"
astr(1, 1) = "C:\Shared\Db1.mdb"
astr(1, 2) = "tblOne"
astr(2, 0) = "tblTest3"
astr(2, 1) = "C:\Shared\Book2.xls"
astr(2, 2) = "Sheet1$"
astr(3, 0) = "tblTest4"
astr(3, 1) = "C:\Shared\Db2.mdb"
astr(3, 2) = "tblTwo"
GetArray = astr
End Function
Private Function LinkTable(strLinkTable As String, _
strSourcePath As String, _
strSourceTable As String) As Boolean
On Error GoTo Err_Handler
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strType As String
Dim strConnect As String
If Len(strSourcePath) > 3 Then
strType = Right$(strSourcePath, 3)
End If
Select Case strType
Case "xls"
strConnect = strConnect & "Excel 5.0;"
strConnect = strConnect & "HDR=YES;"
strConnect = strConnect & "IMEX=2;"
strConnect = strConnect & "DATABASE=" & strSourcePath & ";"
Case "mdb"
strConnect = strConnect & ";DATABASE=" & strSourcePath & ";"
Case Else
Exit Function
End Select
Set dbs = CurrentDb
Set tdf = New DAO.TableDef
tdf.Name = strLinkTable
tdf.Connect = strConnect
tdf.SourceTableName = strSourceTable
dbs.TableDefs.Append tdf
LinkTable = True
Exit_Handler:
If Not tdf Is Nothing Then
Set tdf = Nothing
End If
If Not dbs Is Nothing Then
Set dbs = Nothing
End If
Exit Function
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No:" & Err.Number
Resume Exit_Handler
End Function
Private Function DataFilesExist(aArray)
On Error GoTo Err_Handler
Dim bln As Boolean
Dim lng As Long
For lng = LBound(aArray, 1) To UBound(aArray, 1) - 1
If Len(Dir(aArray(lng, 1))) = 0 Then
bln = True
Exit For
End If
Next
DataFilesExist = Not bln
Exit_Handler:
Exit Function
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No:" & Err.Number
Resume Exit_Handler
End Function
Private Function DeleteLinks(aArray) As Boolean
On Error GoTo Err_Handler
Dim dbs As DAO.Database
Dim lng As Long
Dim strTableName As String
Dim strConnect As String
Set dbs = CurrentDb
For lng = LBound(aArray, 1) To UBound(aArray, 1) - 1
strConnect = ""
strTableName = aArray(lng, 0)
strConnect = dbs.TableDefs(strTableName).Connect
If Len(strConnect) > 0 Then
dbs.TableDefs.Delete strTableName
End If
Next lng
DeleteLinks = True
Exit_Handler:
If Not dbs Is Nothing Then
Set dbs = Nothing
End If
Exit Function
Err_Handler:
Select Case Err.Number
Case 3265
' An attempt to delete a non-existant table
Resume Next
Case Else
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Select
End Function
Private Function CreateLinks(aArray) As Boolean
On Error GoTo Err_Handler
Dim dbs As DAO.Database
Dim lng As Long
Dim strLinkTable As String
Dim strSourcePath As String
Dim strSourceTable As String
Dim blnError As Boolean
Set dbs = CurrentDb
For lng = LBound(aArray, 1) To UBound(aArray, 1) - 1
strLinkTable = aArray(lng, 0)
strSourcePath = aArray(lng, 1)
strSourceTable = aArray(lng, 2)
If Not LinkTable(strLinkTable, strSourcePath, strSourceTable) Then
blnError = True
Exit For
End If
Next
CreateLinks = Not blnError
Exit_Handler:
If Not dbs Is Nothing Then
Set dbs = Nothing
End If
Exit Function
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Function
Public Function CheckLinks() As String
On Error GoTo Err_Handler
Dim a
Dim strErrMsg As String
strErrMsg = "Error Linking Data"
a = GetArray()
If Not DataFilesExist(a) Then
strErrMsg = "External data files cannot be found"
GoTo Exit_Handler
End If
If Not DeleteLinks(a) Then
strErrMsg = "Error deleting existing links"
GoTo Exit_Handler
End If
If Not CreateLinks(a) Then
strErrMsg = "Error creating new links"
GoTo Exit_Handler
End If
strErrMsg = ""
Exit_Handler:
If Len(strErrMsg) > 0 Then
MsgBox strErrMsg, vbCritical
DoCmd.Quit
End If
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Function
Here is some code which you can past into a new module. I
