database connection vba ADODB & ADOX

[liam_oconnell]

ok, so this may be basic since i'm not really a programmer- but when i try to
run the following code to connect to a DB via an EXCEL spreadsheet, it seems
to work fine until i get to the "cat.Tables.Append tbl" line near the end of
the code. At which point i get error 3251 "Object or provider is not capable
of performing requested operation". if i debug on the cnn variable i see 1
error "Driver's SQLConnectAttr failed" if i use the MSDASQL/Access connection
or an "invalid connection string attribute" for the SQLOLEDB/SQL Server
connection (commented out). but the strange thing is that if i check
cnn.state it's equal to 1 after i try to open the connection (i believe
indicating that the connection is actually open since it's 0 before). and i
can see via windows explorer that the code creates a 1k test.lmd file in the
directory where test.mdb resides (the access db i'm trying to connect to).
so on one hand it looks like the connection occurs but there's an error
message in the cnn debug screen indicating that the connection failed.



Private Sub CommandButton1_Click()

Dim cnn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim ky As ADOX.Key

Set cnn = New ADODB.Connection

cnn.Provider = "MSDASQL"
cnn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" & _
"DBQ=C:\save space\workspace\funds\TIPS\test.mdb; ReadOnly=False;"
cnn.Open

'cnn.Provider = "SQLOLEDB"
'cnn.ConnectionString = "Driver={SQL Server};" & _
' "server=FIT-OCONNLC-P01\SQLEXPRESS; database=HistoricalData;
Trusted_Connection=yes; "
'cnn.Open

Set cat = New ADOX.Catalog
cat.ActiveConnection = cnn

Set tbl = New ADOX.Table
tbl.Name = "tblTBond"

tbl.Columns.Append "SecurityDes", adVarChar, 100
tbl.Columns.Append "Issuer", adVarChar, 50
tbl.Columns.Append "IssueDate", adDBDate
tbl.Columns.Append "MaturityDate", adDBDate
tbl.Columns.Append "Coupon", adDouble

cat.Tables.Append tbl
cat.Tables.Refresh

Set cat.ActiveConnection = Nothing
cnn.Close

Set cat = Nothing
Set tbl = Nothing
Set ky = Nothing


End Sub

 

[joel]

why don't you add the table to the database and then add the columns?

Set cat = New ADOX.Catalog
cat.ActiveConnection = cnn

Set tbl = cat.Tables.Append
tbl.Name = "tblTBond"


tbl.Columns.Append "SecurityDes", adVarChar, 100
tbl.Columns.Append "Issuer", adVarChar, 50
tbl.Columns.Append "IssueDate", adDBDate
tbl.Columns.Append "MaturityDate", adDBDate
tbl.Columns.Append "Coupon", adDouble

cat.Tables.Refresh

 

[liam_oconnell]

Thanks Joel- that was helpful.

Liam O'Connell

why don't you add the table to the database and then add the columns?

Set cat = New ADOX.Catalog
cat.ActiveConnection = cnn

Set tbl = cat.Tables.Append
tbl.Name = "tblTBond"


tbl.Columns.Append "SecurityDes", adVarChar, 100
tbl.Columns.Append "Issuer", adVarChar, 50
tbl.Columns.Append "IssueDate", adDBDate
tbl.Columns.Append "MaturityDate", adDBDate
tbl.Columns.Append "Coupon", adDouble

cat.Tables.Refresh