Create relationship as each excel table is imported into db

[tiffany]

Hello,

I’m trying to combine a couple of Subs that I’ve found online. The first one imports every excel file within a specified folder (in the example below, this is ‘strPath’) into Access (but doesn’t set any primary key). The second Sub is supposed to create a relationship between 2 tables.

What I want to happen is this: I have one master table (called “wgsmapsuscur”) already in my access db. This table has a primary key field called “VALUE”.

As I import each excel file into the db, I want to link each new table to my master table (each new table also has a field named “VALUE”). I’d like for the relationship to be such that every record from my master table is shown (e.g., in any query that I might run).

What I’ve tried so far is to call the 2nd Sub (“Sub NewRelation”) in near the end of my 1st Sub (“Sub sImportExcel”), after an excel file has been imported. The 1st Sub works great, but then after one table has been imported, I get the following error:

Run-Time Error “3001” Invalid argument, which refers down to the 2nd Sub where the Set rel = dbs.CreateRelation("valueLink", "wgsmapsuscur", strTable) line is.

Access tells me that strTable = Empty

Does anyone have any suggestions?

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Option Compare Database

Sub sImportExcel()


Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean


' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\WS\Scratch\mapss_zonal_stats\GFD\"

' Replace tablename with the real name of the table into which
' the data are to be imported


strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
strTable = strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

strFile = Dir()
Call NewRelation

Loop

End Sub


Sub NewRelation()
Dim dbs As Database, rel As Relation, fld As Field

' Return reference to current database.
Set dbs = CurrentDb
' Create new Relation object and specify foreign table.
Set rel = dbs.CreateRelation("valueLink", "wgsmapsuscur", strTable)
' Set attributes to enforce referential integrity.
rel.Attributes = dbRelationUpdateCascade And dbRelationDeleteCascade
' Create field in Relation object.
Set fld = rel.CreateField("VALUE")

' Specify field name in foreign table.
fld.ForeignName = "VALUE"
' Append Field object to Fields collection of Relation object.
rel.Fields.Append fld
' Append Relation object to Relations collection.
dbs.Relations.Append rel
dbs.Relations.Refresh
Set dbs = Nothing
End Sub

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

 

[lark_tilly]

I don't believe the 2nd sub knows the value of the strTable variable from the 1st sub.
The value is local to the 1st sub. Try changing your code to Call NewRelation(strTable)
and the name of the 2nd sub to NewRelation(strTable as String). That should pass
the strTable value from the 1st sub to the 2nd sub.

 

[tiffany]

Thanks for the tip... it does get me a little closer, but here's what happens:

Now when I run the first Sub, the 1st excel table from the folder is imported, and the 2nd Sub APPEARS to work, but when I go to look at 'Relationships', no tables exist there. Also, after the first time the 2nd Sub runs, I get a Run-time error '3012': Object [FONT=&quot]"valueLink"[/FONT] already exists. "valueLink" is the name I gave to the Name part of the CreateRelation command. It seems like I need to give this a name that will change with each iteration, but I tried strTable, and that was not a valid name. Also, there's the question of why no relationships appear to have been made??? When I click the Debug button, access points me to the dbs.Relations.Append rel line.

I don't believe the 2nd sub knows the value of the strTable variable from the 1st sub.
The value is local to the 1st sub. Try changing your code to Call NewRelation(strTable)
and the name of the 2nd sub to NewRelation(strTable as String). That should pass
the strTable value from the 1st sub to the 2nd sub.

 

[lark_tilly]

Before we try to dig into this any further I have a couple of questions.
Are all of the excel files identical as far as the columns go? What I'm trying
to figure out is what you're trying to accomplish by creating all these relationships.
Is there like a file for each day or sales region or something? If the data being
imported each time is similar, then perhaps importing the data into an existing table
is a better approach. Just trying to understand what's going on before I or someone
else pursues it further.