By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,482 Members | 3,157 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,482 IT Pros & Developers. It's quick & easy.

Multiple File Types Link Refreshing

P: n/a
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 the
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 the
same directory as the current database.

I do not need or want the ability to open a dialog box to pick the location.
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 backend
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 and
including the developer and northwind databases from Microsoft. Non of them
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
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a

"Scott Tilton" <st*****@avantekservices.com> wrote in message
news:vo************@corp.supernews.com...
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 the 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 the same directory as the current database.

I do not need or want the ability to open a dialog box to pick the location. 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 backend 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 and including the developer and northwind databases from Microsoft. Non of them 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

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\Db1.mdb"
"C:\Database\Db2.mdb"
"C:\Accounts\XL1.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
Nov 12 '05 #2

P: n/a
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

"Fletcher Arnold" <fl****@home.com> wrote in message
news:bm**********@hercules.btinternet.com...

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\Db1.mdb"
"C:\Database\Db2.mdb"
"C:\Accounts\XL1.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

Nov 12 '05 #3

P: n/a
"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



Nov 12 '05 #4

P: n/a
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 strPartsSpreadsheet As String
Dim strBackendDatabase As String
Dim strCommonPath As String

strINIDir = CurrentDBDir()
'Excel Data
strPartsSpreadsheet = sGetINI(strINIDir & "ServCall2.ini", "Excel Data",
"Parts Spreadsheet", "")
'Access Data
strBackendDatabase = sGetINI(strINIDir & "ServCall2.ini", "Access Data",
"Backend Database", "")
'GoldMine Data
strCommonPath = sGetINI(strINIDir & "ServCall2.ini", "GoldMine Data",
"Common Path", "")

Dim astr(5, 3) As String

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

astr(1, 0) = "Service_Calls"
astr(1, 1) = strBackendDatabase
astr(1, 2) = "Service_Calls"

astr(2, 0) = "Service_Parts"
astr(2, 1) = strBackendDatabase
astr(2, 2) = "Service_Parts"

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

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

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 "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.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
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(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


Nov 12 '05 #5

P: n/a
"Scott Tilton" <st*****@avantekservices.com> wrote in message
news:vo************@corp.supernews.com...
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?

Well you could create them manually and then inspect the connect property by
getting the immediate window (CTRL-G) and typing in
?Currentdb.TableDefs("tblMyDbfTable").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

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.