Multiple File Types Link Refreshing

**Fletcher Arnold** · Nov 12 '05, 03:10 PM

Re: Multiple File Types Link Refreshing

"Scott Tilton" <stilton@avante kservices.com> wrote in message
news:vobbp8os2j jh4b@corp.super news.com...[color=blue]
> I am having a terrible time getting this to work. I am hoping someone out
> there can help me with very specific code examples. I am trying to get[/color]
the[color=blue]
> linked tables in my Access 97 database to be updated whenever the database
> opens. I need to have them updated based on an INI file that resides in[/color]
the[color=blue]
> same directory as the current database.
>
> I do not need or want the ability to open a dialog box to pick the[/color]
location.[color=blue]
> I simply want it to updated based on the INI file, if it still does not
> work, then it pops up an error and closes access.
>
> The real tricky part I am having problems with is I have multiple linked
> tables. From 3 different sources and types. Not all from the same[/color]
backend[color=blue]
> access database.
>
> I have 2 different linked DBASE databases.
> I have multiple tables from a single access backend.
> I also have 1 linked excel spreadsheet.
>
> All three of these are located in 3 separate locations. All three have
> their file names and paths located in an INI file.
>
> I have tried to take examples from everything I can find on the Internet[/color]
and[color=blue]
> including the developer and northwind databases from Microsoft. Non of[/color]
them[color=blue]
> seem to be able to get me to where I want to be. Most of them all asume
> either you are dealing with one linked database with all the tables in it.
>
> I really need to get this to work. Is there anyone who can give me some
> seriously needed help with doing this?
>
> Thanks,
>
> Scott Tilton[/color]

Many people here could write a routine for that - but perhaps you could make
a start yourself, then post the code you are having trouble with. For
example, what is the general strategy?

Function GetFileArray()
Reads the file and returns an array like:
"C:\Database\Db 1.mdb"
"C:\Database\Db 2.mdb"
"C:\Accounts\XL 1.xls"

Check these files exist

Delete existing links

Create new linked tables

This is one strategy - based on the fact that from experience, deleting and
recreating linked tables can be more efficient than trying to refresh links
the links. It also does not bother to check if the linked tables need to be
adjusted - perhaps it should.

Fletcher

**Scott Tilton** · Nov 12 '05, 03:11 PM

Re: Multiple File Types Link Refreshing

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\Ideacom m\SrvCall2\
Spreadsheet File=IdeacomPar tsPricing.xls
[GoldMine Data]
Contact1 Path=N:\Client GoldMines\Ideac om_GoldMine_4\C ommon\
Contact2 Path=N:\Client GoldMines\Ideac om_GoldMine_4\C ommon\
[Access Data]
Backend Database Path=L:\Client Holding\Ideacom m\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

"Fletcher Arnold" <fletch@home.co m> wrote in message
news:bm5qdp$r8i $1@hercules.bti nternet.com...[color=blue]
>
> Many people here could write a routine for that - but perhaps you could[/color]
make[color=blue]
> a start yourself, then post the code you are having trouble with. For
> example, what is the general strategy?
>
> Function GetFileArray()
> Reads the file and returns an array like:
> "C:\Database\Db 1.mdb"
> "C:\Database\Db 2.mdb"
> "C:\Accounts\XL 1.xls"
>
> Check these files exist
>
> Delete existing links
>
> Create new linked tables
>
> This is one strategy - based on the fact that from experience, deleting[/color]
and[color=blue]
> recreating linked tables can be more efficient than trying to refresh[/color]
links[color=blue]
> the links. It also does not bother to check if the linked tables need to[/color]
be[color=blue]
> adjusted - perhaps it should.
>
> Fletcher
>
>[/color]

**Fletcher Arnold** · Nov 12 '05, 03:12 PM

Re: Multiple File Types Link Refreshing

"Scott Tilton" <stilton@avante kservices.com> wrote in message
news:vod9qj5pr5 vtc7@corp.super news.com...[color=blue]
> Thank you for your reply. The only start that I have is trying to adapt[/color]
the[color=blue]
> linking and refreshing of linked tables found in the sample databases
> provided by Microsoft. However, since this does not address the issues I[/color]
am[color=blue]
> 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\Ideacom m\SrvCall2\
> Spreadsheet File=IdeacomPar tsPricing.xls
> [GoldMine Data]
> Contact1 Path=N:\Client GoldMines\Ideac om_GoldMine_4\C ommon\
> Contact2 Path=N:\Client GoldMines\Ideac om_GoldMine_4\C ommon\
> [Access Data]
> Backend Database Path=L:\Client Holding\Ideacom m\SrvCall2\
> Backend Database File=ServCall2_ Data.mdb
> [License]
> License Name=Ideacomm
> ----------------------------------------------
>
> I have no trouble getting this information. I bring each one of these[/color]
into[color=blue]
> 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[/color]
go[color=blue]
> with. I just need to know how to address a specific linked table and[/color]
update[color=blue]
> 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[/color]
VBA.[color=blue]
>
> 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[/color]

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\Book 1.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\Book 2.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(strLi nkTable 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(strSourcePa th) > 3 Then
strType = Right$(strSourc ePath, 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.SourceTable Name = strSourceTable

dbs.TableDefs.A ppend 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(aAr ray) 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(s trTableName).Co nnect

If Len(strConnect) > 0 Then
dbs.TableDefs.D elete 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(aAr ray) 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(strLi nkTable, 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

**Scott Tilton** · Nov 12 '05, 03:16 PM

Re: Multiple File Types Link Refreshing

Thank you for you help. This has been really great. It seems to be working
fine for the Excel and Access linked tables, but I am having trouble getting
the dBase 5 links to work. I have included your code back with the
modifications I have made. There are only two locations that I made
changes. I added a "DBF" case to the section function, and modified the
array to pull from the INI. Could you let me know what I am doing wrong?
Thanks.

It has no problem deleting the Dbase links, but can't recreate them. I get
a "error creating new links". I know the path is right because if I change
it, I get an error. So it finds the files. It just won't link to them.
Any ideas?

Also, just a quick question... will deleting and recreating the tables
effect relationships at all? Also, if mutilple people are accessing the
database, how will this effect them? Thanks again for all your help.

-Scott-

----------------------------------------------------------------------------
----------

Private Function GetArray()

Dim strINIDir As String
Dim strPartsSpreads heet As String
Dim strBackendDatab ase As String
Dim strCommonPath As String

strINIDir = CurrentDBDir()
'Excel Data
strPartsSpreads heet = sGetINI(strINID ir & "ServCall2.ini" , "Excel Data",
"Parts Spreadsheet", "")
'Access Data
strBackendDatab ase = sGetINI(strINID ir & "ServCall2.ini" , "Access Data",
"Backend Database", "")
'GoldMine Data
strCommonPath = sGetINI(strINID ir & "ServCall2.ini" , "GoldMine Data",
"Common Path", "")

Dim astr(5, 3) As String

astr(0, 0) = "Parts"
astr(0, 1) = strPartsSpreads heet
astr(0, 2) = "Sheet1$"

astr(1, 0) = "Service_Ca lls"
astr(1, 1) = strBackendDatab ase
astr(1, 2) = "Service_Ca lls"

astr(2, 0) = "Service_Pa rts"
astr(2, 1) = strBackendDatab ase
astr(2, 2) = "Service_Pa rts"

astr(3, 0) = "Contact1"
astr(3, 1) = strCommonPath
astr(3, 2) = "Contact1#D BF"

astr(4, 0) = "Contact2"
astr(4, 1) = strCommonPath
astr(4, 2) = "Contact2#D BF"

GetArray = astr

End Function

Private Function LinkTable(strLi nkTable 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(strSourcePa th) > 3 Then
strType = Right$(strSourc ePath, 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 "dbf"
strConnect = strConnect & "dBASE 5.0;"
strConnect = strConnect & "HDR=NO;"
strConnect = strConnect & "IMEX=2;"
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.SourceTable Name = strSourceTable

dbs.TableDefs.A ppend 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(aAr ray) 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(s trTableName).Co nnect

If Len(strConnect) > 0 Then
dbs.TableDefs.D elete 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
MsgBox "There was an error deleting the linked tables. Check your INI
settings.", vbCritical, "Failed"
Resume Next

Case Else
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Select

End Function

Private Function CreateLinks(aAr ray) 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(strLi nkTable, 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

**Fletcher Arnold** · Nov 12 '05, 03:18 PM

Re: Multiple File Types Link Refreshing

"Scott Tilton" <stilton@avante kservices.com> wrote in message
news:volbcr10eg 2408@corp.super news.com...[color=blue]
> Thank you for you help. This has been really great. It seems to be[/color]
working[color=blue]
> fine for the Excel and Access linked tables, but I am having trouble[/color]
getting[color=blue]
> the dBase 5 links to work. I have included your code back with the
> modifications I have made. There are only two locations that I made
> changes. I added a "DBF" case to the section function, and modified the
> array to pull from the INI. Could you let me know what I am doing wrong?
> Thanks.
>
> It has no problem deleting the Dbase links, but can't recreate them. I[/color]
get[color=blue]
> a "error creating new links". I know the path is right because if I[/color]
change[color=blue]
> it, I get an error. So it finds the files. It just won't link to them.
> Any ideas?
>[/color]

Well you could create them manually and then inspect the connect property by
getting the immediate window (CTRL-G) and typing in
?Currentdb.Tabl eDefs("tblMyDbf Table").Connect
Although your connection string looks ok to me, I know there are some
special considerations when linking to dbf files. On thing I notice is that
if you create the link manually, then you are promted to provide the name of
the idx file and also the unique record identifier - these details are then
stored in an .ini file in the data folder. The same does not seem to happen
if you create the link using DAO coding.
To start with I would check out:
http://www.microsoft.com/AccessDev/Articles/AccExt.htm
and go to the section "Accessing FoxPro and dBASE Data" - it may be of some
help.
It might also be an idea to re-post any specific dbf linked table questions
since its years since I have worked with these files and you may get a more
qualified opinion elsewhere.

Good luck

Fletcher

Multiple File Types Link Refreshing

Multiple File Types Link Refreshing

Comment

Comment

Comment

Comment

Comment