Solved: missing reference

[gibbo1715]

All

My spreadsheet has a reference to Microsoft ADO ext. 2.7 for dll and security that is missing on my works computer, is there a simple way around this problem?

Cheers

Gibbo

[Bob Phillips]

Select an earlier version irf there is one, otherwise, from your machine find the file name and browse for it on the work's computer.

[gibbo1715]

XLD

Thanks for the reply

I am going to be using this across a network and not many of the computers seem to be able to deal with this so was gonna go along the lines of late binding as follows

[VBA] Sub Create_MDB_Tables_On_The_Fly()
Dim xCat As Object 'ADOX.Catalog
Dim xTable As Object 'ADOX.Table
Dim wsSheet As Worksheet
Dim cTables As Collection
Dim vaValues As Variant
Dim lnRows As Long
Dim i As Long, j As Long, k As Long

With ActiveWorkbook
Set wsSheet = .Worksheets(1)
End With

With wsSheet
lnRows = .Range("C65536").End(xlUp).Row
'Populate the array.
vaValues = .Range("C2:G" & lnRows).Value
End With


Set xCat = CreateObject("ADOX.Catalog")
Set xTable = CreateObject("ADOX.Table")
On Error Resume Next
Kill stDB
On Error GoTo 0

'Create the MDB.
xCat.Create (stCon)

'Populate the collection with unique Table names.
On Error Resume Next
For i = 1 To UBound(vaValues)
cTables.Add vaValues(i, 1), CStr(vaValues(i, 1))
Next i
' On Error Resume Next

For k = 1 To cTables.Count
Set xTable = CreateObject("ADOX.Table")
With xTable
'Name the table.
.Name = "tbl_" & cTables(k)
'Create the field which also is the Primary Key (PK) field for the Table.
.Columns.Append "ID" ', adInteger
'In order to access the properties we need to set the Parent Catalog.
.ParentCatalog = xCat
.Columns("ID").Properties("AutoIncrement").Value = True
'Append the PK.
.Keys.Append "PrimaryKey", adKeyPrimary, "ID"
'Loop through the variable vaValues and append these fields to the Table.
For j = 1 To UBound(vaValues)
If vaValues(j, 1) = cTables(k) Then
If vaValues(j, 3) = "Integer" Then
.Columns.Append vaValues(j, 2), adColFixed
ElseIf vaValues(j, 3) = "Decimal" Then
.Columns.Append vaValues(j, 2), adNumeric
.Columns(vaValues(j, 2)).Precision = CLng(vaValues(j, 5))
ElseIf vaValues(j, 3) = "Date" Then
.Columns.Append vaValues(j, 2), adDate
Else 'Textfields
.Columns.Append vaValues(j, 2), adWChar, CLng(vaValues(j, 5))
End If
End If
Next j
End With
xCat.Tables.Append xTable
Set xTable = Nothing
Next k

Set cTables = Nothing
Set xTable = Nothing: Set xCat = Nothing

MsgBox "The database have successfully been updated!"
End Sub [/VBA]

My problem is i get an error at "adKeyPrimary" saying object or property not recognised, so I assume i have to set the properties for all the adColFixed, ad numeric etc but can anyone tell me what I have to set them too?

Thanks

Gibbo

[Bob Phillips]

My problem is i get an error at "adKeyPrimary" saying object or property not recognised, so I assume i have to set the properties for all the adColFixed, ad numeric etc but can anyone tell me what I have to set them too?

These are ADO constants, which are just symbolic names for numbers. You can't use these constants unless you have a reference to the library which defines them. Since you are using late binding, you don't have a reference to the ADO DDL & Security library, so you have to use the actual numeric value rather than the constant name.

There is a well tried technique for doing this, described at http://www.xldynamic.com/source/xld.EarlyLate.html. It is based upon an Outlook application, but the principle is the same.

[gibbo1715]

Thanks,

Had a read and get the principle but not sure how to establish the numeric value, Can you point me in the right direction please?

I am considering changing my approach to DAO as this older method is available on all my machines but I am sure there are pros and cons to this method so would appreciate your thoughts,

This is new ground for me so sorry for my lack of knowledge

Cheers

Gibbo

[Bob Phillips]

Had a read and get the principle but not sure how to establish the numeric value, Can you point me in the right direction please?

I am considering changing my approach to DAO as this older method is available on all my machines but I am sure there are pros and cons to this method so would appreciate your thoughts,

Gibbo,

The way to determine those constants is to go onto your machine, and set a reference to the ADO DDL & Security library. Then in the immediate window just get the values by typing the constant name, such as
?adKeyPrimary.

Then you just substitute the values for the constants.

Doing that, I get these values

?adKeyPrimary
1
?adColFixed
1
?adNumeric
131
?adDate
7
?adWChar
130

[gibbo1715]

Thanks XLD

That works for me,

Can i just ask what adInteger would be as I cant get near my machine for about 5 hours

Then I ll post my finished code and mark this as solved

Thanks Again

Gibbo

[Bob Phillips]

Can i just ask what adInteger would be as I cant get near my machine for about 5 hours

Then I ll post my finished code and mark this as solved

adInteger has a value of 3

[gibbo1715]

Hugh thanks to XL Dennis (Who wrote the original version of the code below) and to XLD for helping me change it to late binding.

I still have a problem with naming the tables from a worksheet and get an error at the point shown below saying object variable or with variable not set, but the rest of the code now works great

Any ideas how to fix this last problem?

[VBA] Option Explicit
'Add references to the following external libraries
'Microsoft ActiveX Data Object 2.5 Library and later.
Const stDB As String = "C:\Gibbos.mdb"
Const stCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & stDB & ";"

Sub Create_MDB_Tables_On_The_Fly()
Dim xCat As Object 'ADOX.Catalog
Dim xTable As Object 'ADOX.Table
Dim wsSheet As Worksheet
Dim cTables As Collection
Dim vaValues As Variant
Dim lnRows As Long
Dim i As Long, j As Long, k As Long

With ActiveWorkbook
Set wsSheet = .Worksheets(1)
End With

With wsSheet
lnRows = .Range("C65536").End(xlUp).Row
'Populate the array.
vaValues = .Range("C2:G" & lnRows).Value
End With
Set xCat = CreateObject("ADOX.Catalog")
'Delete the existing MDB, if any.
'On Error Resume Next
Kill stDB
' On Error GoTo 0

'Create the MDB.
xCat.Create (stCon)

'Here we use an existing MDB and therefore we just connect to it.
'xCat.ActiveConnection = stCon

'Populate the collection with unique Table names.
On Error Resume Next
For i = 1 To UBound(vaValues)
cTables.Add vaValues(i, 1), CStr(vaValues(i, 1)) <<< Error Here
Next i
On Error Resume Next

'Loop through the collection of unique Table names and
'append the Tables and the fields to the MDB.
For k = 1 To cTables.Count
Set xTable = CreateObject("ADOX.Table")
With xTable
'Name the table.
.Name = "tbl_" & cTables(k)
'Create the field which also is the Primary Key (PK) field for the Table.
.Columns.Append "ID", 3
'In order to access the properties we need to set the Parent Catalog.
.ParentCatalog = xCat
.Columns("ID").Properties("AutoIncrement").Value = True
'Append the PK.
.Keys.Append "PrimaryKey", 1, "ID"
'Loop through the variable vaValues and append these fields to the Table.
For j = 1 To UBound(vaValues)
If vaValues(j, 1) = cTables(k) Then
If vaValues(j, 3) = "Integer" Then
.Columns.Append vaValues(j, 2), adInteger
ElseIf vaValues(j, 3) = "Decimal" Then
.Columns.Append vaValues(j, 2), 131
.Columns(vaValues(j, 2)).Precision = CLng(vaValues(j, 5))
ElseIf vaValues(j, 3) = "Date" Then
.Columns.Append vaValues(j, 2), 7
Else 'Textfields
.Columns.Append vaValues(j, 2), 130, CLng(vaValues(j, 5))
End If
End If
Next j
'If we want to completely hide the Table when the MDB is open via MS Access
'we can set the following property.
'.Properties("Jet OLEDB:Table Hidden in Access").Value = True
End With
'Append the Table to the MDB.
xCat.Tables.Append xTable
Set xTable = Nothing
Next k
'Release the objects from the memory.
Set cTables = Nothing
Set xTable = Nothing: Set xCat = Nothing
MsgBox "The database have successfully been updated!"
End Sub [/VBA]

Cheers

Gibbo

[Bob Phillips]

Tut, tut! You removed a line

[VBA]
Sub Create_MDB_Tables_On_The_Fly()
Dim xCat As Object 'ADOX.Catalog
Dim xTable As Object 'ADOX.Table
Dim wsSheet As Worksheet
Dim cTables As Collection
Dim vaValues As Variant
Dim lnRows As Long
Dim i As Long, j As Long, k As Long

With ActiveWorkbook
Set wsSheet = .Worksheets(1)
End With

With wsSheet
lnRows = .Range("C65536").End(xlUp).Row
'Populate the array.
vaValues = .Range("C2:G" & lnRows).Value
End With
Set xCat = CreateObject("ADOX.Catalog")
'Delete the existing MDB, if any.
On Error Resume Next
Kill stDB
On Error GoTo 0

'Create the MDB.
xCat.Create (stCon)

'Here we use an existing MDB and therefore we just connect to it.
'xCat.ActiveConnection = stCon

'Populate the collection with unique Table names.
On Error Resume Next
Set cTables = New Collection
For i = 1 To UBound(vaValues)
cTables.Add vaValues(i, 1), CStr(vaValues(i, 1)) '<<< Error Here
Next i
On Error GoTo 0

'Loop through the collection of unique Table names and
'append the Tables and the fields to the MDB.
For k = 1 To cTables.Count
Set xTable = CreateObject("ADOX.Table")
With xTable
'Name the table.
.Name = "tbl_" & cTables(k)
'Create the field which also is the Primary Key (PK) field for the Table.
.Columns.Append "ID", 3
'In order to access the properties we need to set the Parent Catalog.
.ParentCatalog = xCat
.Columns("ID").Properties("AutoIncrement").Value = True
'Append the PK.
.Keys.Append "PrimaryKey", 1, "ID"
'Loop through the variable vaValues and append these fields to the Table.
For j = 1 To UBound(vaValues)
If vaValues(j, 1) = cTables(k) Then
If vaValues(j, 3) = "Integer" Then
.Columns.Append vaValues(j, 2), 3
ElseIf vaValues(j, 3) = "Decimal" Then
.Columns.Append vaValues(j, 2), 131
.Columns(vaValues(j, 2)).Precision = CLng(vaValues(j, 5))
ElseIf vaValues(j, 3) = "Date" Then
.Columns.Append vaValues(j, 2), 7
Else 'Textfields
.Columns.Append vaValues(j, 2), 130, CLng(vaValues(j, 5))
End If
End If
Next j
'If we want to completely hide the Table when the MDB is open via MS Access
'we can set the following property.
'.Properties("Jet OLEDB:Table Hidden in Access").Value = True
End With
'Append the Table to the MDB.
xCat.Tables.Append xTable
Set xTable = Nothing
Next k
'Release the objects from the memory.
Set cTables = Nothing
Set xTable = Nothing: Set xCat = Nothing
MsgBox "The database have successfully been updated!"
[/VBA]

[gibbo1715]

Ok Not sure how i managed that, (Or didnt spot I had for that matter)

Thanks again

Now solved

Gibbo