Bouldergirl
Technical User
Hello World,
As I can barely write code that prints 'Hello World', I am in need of some assistance:
So I've got a module that basically imports every excel table in a particular folder into a db. Each table is named according to its excel filename. Each table also has 2 fields: 'VALUE' & 'COUNT'.
Then there is another table, a 'Master' table. This table also has a 'VALUE' field.
I want to get some script which will go through my db and for every table (except the Master table), automatically link (or create a relationship?) to the 'Master' table based on the 'VALUE' fields. Referential integrity is not important, as these tables won't ever be modified. But I do care about the join properties:
I want to include ALL records from 'Master' and only those from the excel-derived tables where the joined fields are equal.
I tried to do this as I was importing each excel table (See code below), but I am having a really hard time.
It seems that the first Excel table is succesfully imported, but then after the first time the 'NewRelation' Sub is run, I get an error message: "Run-time error '3012' Object 'myLink' already exists."
So the Name given to the CreateRelation has to change, which I guess means that this Sub should also be a loop and I have to find a way to make the 'Name' field of CreateRelation variable.
A couple options occur to me, though I know not how to implement any of them programmatically:
1) Could I just import all my excel tables and then run a code that links them all to my 'Master' table? One unique thing about the excel table names as they appear in access is that each Table name ends with "_xls", so this characteristic could maybe be used to distinguish them from the 'Master' table when making the relationships?
2) Maybe I'm going about this the wrong way... I could also append the columns from each excel table into my Master table, but then since each excel file has the same 2 field names, I'd need to tack something unique onto each field name (maybe derived from the original excel table's name?) before appending each new column?
3) Maybe the code below isn't too far off from where it needs to be and someone can suggest how to fix it?
THANK-YOU in advance!
-Tiffany
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
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
Call NewRelation(strTable)
strFile = Dir()
Loop
End Sub
Sub NewRelation(strTable 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("myLink", "Master", 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
As I can barely write code that prints 'Hello World', I am in need of some assistance:
So I've got a module that basically imports every excel table in a particular folder into a db. Each table is named according to its excel filename. Each table also has 2 fields: 'VALUE' & 'COUNT'.
Then there is another table, a 'Master' table. This table also has a 'VALUE' field.
I want to get some script which will go through my db and for every table (except the Master table), automatically link (or create a relationship?) to the 'Master' table based on the 'VALUE' fields. Referential integrity is not important, as these tables won't ever be modified. But I do care about the join properties:
I want to include ALL records from 'Master' and only those from the excel-derived tables where the joined fields are equal.
I tried to do this as I was importing each excel table (See code below), but I am having a really hard time.
It seems that the first Excel table is succesfully imported, but then after the first time the 'NewRelation' Sub is run, I get an error message: "Run-time error '3012' Object 'myLink' already exists."
So the Name given to the CreateRelation has to change, which I guess means that this Sub should also be a loop and I have to find a way to make the 'Name' field of CreateRelation variable.
A couple options occur to me, though I know not how to implement any of them programmatically:
1) Could I just import all my excel tables and then run a code that links them all to my 'Master' table? One unique thing about the excel table names as they appear in access is that each Table name ends with "_xls", so this characteristic could maybe be used to distinguish them from the 'Master' table when making the relationships?
2) Maybe I'm going about this the wrong way... I could also append the columns from each excel table into my Master table, but then since each excel file has the same 2 field names, I'd need to tack something unique onto each field name (maybe derived from the original excel table's name?) before appending each new column?
3) Maybe the code below isn't too far off from where it needs to be and someone can suggest how to fix it?
THANK-YOU in advance!
-Tiffany
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
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
Call NewRelation(strTable)
strFile = Dir()
Loop
End Sub
Sub NewRelation(strTable 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("myLink", "Master", 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
