Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
database connection vba ADODB & ADOX
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
database connection vba ADODB & ADOX
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
database connection vba ADODB & ADOX
Thanks Joel- that was helpful.
Liam O'Connell "joel" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ADODB Connection | Excel Worksheet Functions | |||
Set cnn = New ADODB.Connection | Excel Programming | |||
ADODB.Connection Error | Excel Programming | |||
ADODB.Connection | Excel Programming | |||
ADODB.Connection | Excel Programming |