By allowing Access 2000 to do its thing I see that I appear to have duplicate field names in the table, and worse, I don't know the algorithm for mapping from a string label to a usable Field name.
If anyone can point me to the rules that Access follows in deriving a field name via the CREATE TABLE I'd be grateful.
In the end, I want to locate the labels in any downloaded sheet and be able to pop the associated numeric values into the appropriate fields.
I would prefer to derive the field name by algorithm rather than by a table mapping, so that the VBA code remains nicely generic.
If all else fails I could write my own label-to-identifier mapping function.
Code: Select all
Sub BuildDbase()
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Create an Access database on the fly
'Don't forget to set a reference to the Microsoft ActiveX Data Objects Library
' http://www.excelguru.ca/node/60
' Cloned Chris Greaves feb 4th 2010
Dim dbConnectStr As String
Dim Catalog As Object
Dim cnt As ADODB.Connection
Dim dbPath As String
Dim strMDBFullname As String
strMDBFullname = ThisWorkbook.Path & "\" & "Financial.mdb"
dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strMDBFullname & ";"
If UX.blnFileExists(strMDBFullname) Then
Else
Call amlog(ThisWorkbook, "I cannot find your database " & strMDBFullname, True, True)
'Create new database
Set Catalog = CreateObject("ADOX.Catalog")
Catalog.Create dbConnectStr
Set Catalog = Nothing
End If
'Connect to database and insert a new table
Set cnt = New ADODB.Connection
With cnt
.Open dbConnectStr
Dim strExecute As String
strExecute = "CREATE TABLE tblsample (" & strFieldnames & ")"
.Execute strExecute
End With
Set cnt = Nothing
End Sub