Verifying linked table source files exist

[Walt H]

Hello All! I have an access db that is comprised of many tables which are
linked to flat files located in a static disk location. I then have a union
query which stacks the data from each linked table. The problem is that
should one of the underlying linked files not exist the union query fails.
I suppose I could write code to physically import each source file and use
error handling to ignore files which do not exist. But I was wondering if
there might be an alternative out there that might be more expedient. Thanks
in advance for your consideration of this problem!

Regards,

Walt

 

[Marshall Barton]

Hello All! I have an access db that is comprised of many tables which are
linked to flat files located in a static disk location. I then have a union
query which stacks the data from each linked table. The problem is that
should one of the underlying linked files not exist the union query fails.
I suppose I could write code to physically import each source file and use
error handling to ignore files which do not exist. But I was wondering if
there might be an alternative out there that might be more expedient.

Dim db As Database
Dim tdf As TableDef
Dim SQL As String

Set db = CurrentDb()

For Each tdf In db.TableDefs
If tdf.Connect <> "" Then
If Dir(tdf.Connect) <> "" Then
SQL = SQL & "UNION SELECT * FROM " & tdf.Name
End If
End If
Next tdf

SQL = Mid(SQL, 7)
' do whatever with the query

 

[Walt H]

Marshalll, belated thanks for the help. A bit over my head but are you
suggesting this code would be invoked prior to running my union query. Not
sure what to do with what you've provided and it is definitely me not you.
Been trying to do some volunteer work for the Red Cross and my bailiwick is
more SQL and Acctg DB's than Access programming....

 

[Marshall Barton]

Marshalll, belated thanks for the help. A bit over my head but are you
suggesting this code would be invoked prior to running my union query. Not
sure what to do with what you've provided and it is definitely me not you.
Been trying to do some volunteer work for the Red Cross and my bailiwick is
more SQL and Acctg DB's than Access programming....

Your UNION query did not do the job, so my suggested code
tries to construct a query that only uses linked tables.

I just noticed that I forgot to extract the path to the
linked text file, so it's a little more complicated than
what I posted earlier. Try this:

Dim db As Database
Dim tdf As TableDef
Dim strSQL As String

Set db = CurrentDb()

For Each tdf In db.TableDefs
If tdf.Connect <> "" Then
If Dir(Mid(InStr(tdf.Connect, ";Database=") + 11) _
& "\" & tdf.SourceTableName) <> "" Then
strSQL = strSQL & "UNION SELECT * FROM " & tdf.Name
End If
End If
Next tdf

strSQL = Mid(strSQL, 7)

You never did say what you are doing with the query, so I
can't tell what you want after the above. If you are using
the query as a form/report's record source, then add the
line:
Me.RecordSource = strSQL
and put the code in the form/report's Open event procedure.

If you prefer to modify your existing query then add this
code:
CurrentDb.QueryDefs![yourquery].SQL = strSQL
and run the code (via a form command button?) before you use
your query.

 

[Walt H]

Marshall, many thanks for your help. I am most appreciative.

Regards,

Walt

Marshalll, belated thanks for the help. A bit over my head but are you
suggesting this code would be invoked prior to running my union query. Not
sure what to do with what you've provided and it is definitely me not you.
Been trying to do some volunteer work for the Red Cross and my bailiwick is
more SQL and Acctg DB's than Access programming....

Your UNION query did not do the job, so my suggested code
tries to construct a query that only uses linked tables.

I just noticed that I forgot to extract the path to the
linked text file, so it's a little more complicated than
what I posted earlier. Try this:

Dim db As Database
Dim tdf As TableDef
Dim strSQL As String

Set db = CurrentDb()

For Each tdf In db.TableDefs
If tdf.Connect <> "" Then
If Dir(Mid(InStr(tdf.Connect, ";Database=") + 11) _
& "\" & tdf.SourceTableName) <> "" Then
strSQL = strSQL & "UNION SELECT * FROM " & tdf.Name
End If
End If
Next tdf

strSQL = Mid(strSQL, 7)

You never did say what you are doing with the query, so I
can't tell what you want after the above. If you are using
the query as a form/report's record source, then add the
line:
Me.RecordSource = strSQL
and put the code in the form/report's Open event procedure.

If you prefer to modify your existing query then add this
code:
CurrentDb.QueryDefs![yourquery].SQL = strSQL
and run the code (via a form command button?) before you use
your query.