ADO Table Creation (MS Access)

[Citron]

Does anyone out there have any sample code that shows how to create a table in a MS Access Database using an ADO connection?

[Iouri]

'reference to ADOX and ADO

Dim catAccess As New ADOX.Catalog
Dim tblValExp As New ADOX.Table
Dim cnnAccess As New ADODB.Connection
Dim rstValExp As New ADODB.Recordset
catAccess.Create "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=c:\testbed\test1.mdb"
catAccess.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=c:\testbed\test1.mdb"
With tblValExp
.Name = "tblValExp1"
.Columns.Append "Stock Name", adVarWChar, 40
.Columns.Append "Stock Symbol", adVarWChar, 6 '1
.Columns.Append "Stock Price", adSingle '2
.Columns.Append "Est Date", adVarWChar, 10 '3
.Columns.Append "Est Year", adVarWChar, 6 '4
.Columns.Append "Price High(0)", adSingle '5
.Columns.Append "Price High(1)", adSingle '6
.Columns.Append "Price High(2)", adSingle '7
.Columns.Append "Price High(3)", adSingle '8
.Columns.Append "Price High(4)", adSingle '9
.Columns.Append "Price High(5)", adSingle '10
End With
catAccess.Tables.Append tblValExp
Set tblValExp = Nothing
Set catAccess = Nothing
cnnAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\testbed\test1.mdb;Persist Security Info=False"
rstValExp.Open "SELECT * FROM tblValExp1", cnnAccess, adOpenKeyset, adLockOptimistic
rstValExp.AddNew
rstValExp.Fields(0).Value = "Name"
rstValExp.Fields(1).Value = "Symbol"
rstValExp.Fields(2).Value = 1.5
rstValExp.Fields(3).Value = Format(Now(), "dd/mm/yyyy")
rstValExp.Fields(4).Value = "2000"
rstValExp.Fields(5).Value = 1.5
rstValExp.Fields(6).Value = 1.5
rstValExp.Fields(7).Value = 1.5
rstValExp.Fields(8).Value = 1.5
rstValExp.Fields(9).Value = 1.5
rstValExp.Fields(10).Value = 1.5
rstValExp.Update
rstValExp.MoveFirst
Set rstValExp = Nothing
Set cnnAccess = Nothing






Iouri Boutchkine
[email protected]

[Citron]

Im getting a "User-defined type not defined" error on the following line:
Dim catAccess As New ADOX.Catalog

Im a bit of a VB Newbie ...

[Iouri]

Did you set reference to ADOX?
Go to References and choose Microsoft ADO Ext.2.1 for DDL and Security.

Don't forget to set reference for ADO
Microsoft ActiveX Data Objects 2.x Library

where 2.x is the latest LIbrary installed on your computer. It can be 2.0, 2.1..etc...

Iouri Boutchkine
[email protected]

[phunkydude]

If you need to create the Database then use the ADOX library.

If the Database already exists there is no need to reference ADOX. Instead, use

connection.Execute "CREATE TABLE tablename ( Field1 Field1DataType, ..., FieldnDataType) CONSTRAINT PrimaryKeyName PRIMARY KEY (KeyField1, ..., KeyFieldn))



You can also add the UNIQUE and NOT NULL constraints (similar to PRIMARY KEY in example).

Similarly you can use

set rsSchema = connection.OpenSchema(adSchemaTables)
Do While Not rsSchema.EOF
If rsSchema.Fields("TABLE_TYPE") = "TABLE" then
connection.Execute "DROP TABLE " & rsSchema.Fields("TABLE_NAME")
End If
rsSchema.MoveNext
Loop



to delete the tables.

Just another you can do it.

[Citron]

Well you were right about the missing referance .... getting closer!
Now Im getting the following error:

Run-time Error '-2147467259(80004005)
Could not find installable ISAM

on the following line:
catAccess.ActiveConnection "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=c:\testbed\test1.mdb"

Any ideas

[Iouri]

What version of VB are you running and if it is VB6 what last Service pack did you install. Check do you have OLEDB provider Jet 4.0. If not then
change the line
catAccess.ActiveConnection "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=c:\testbed\test1.mdb"

to

catAccess.ActiveConnection "Provider=Microsoft.Jet.OLEDB.3.51;DataSource=c:\testbed\test1.mdb"

Iouri Boutchkine
[email protected]