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
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
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
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>