reate relationship as each excel table is imported VBA

[Boulder_girl]

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

 

[Steve Sanford]

"Value" is a reserved word in Access (and SQL) and shouldn't be used as an
object name. For a list of reserved words, see

http://allenbrowne.com/AppIssueBadWord.html


The reason you get the error (Run-Time Error “3001â€) is because strTable
*is* empty..... as far as "Sub NewRelation()" is concerned.

There is a thing called variable scope. The variable "strTable" is declared
in the subroutine "sImportExcel()" and has a value assigned to it. But "Sub
NewRelation()" does not have access to the variables in any other subroutine.

So you have two options. Declare "strTable" as a module level variable or
pass the value to the sub. I prefer to pass the value; the code would look
like this:

(I marked the lines I cahnged with '**** )


.. <snip>
..
strFile = Dir()
Call NewRelation(strTable) '****

Loop

End Sub


Sub NewRelation(pTable As String) '****
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", pTable) '****
..
.. <snip>



Note that in the line "Set rel=......", I changed the last argument from
"strTable" to "pTable". (The p is for parameter)



HTH