-
database connection via adodb/adox in excel
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.
Code:
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
Last edited by Hack; 04-27-2009 at 12:00 PM.
Reason: Added Code Tags
-
I think that you is a bit confused...
- MSDASQL/Access ???
- SQLOLEDB/SQL Server connection ???
- SQLEXPRESS ???
if you use a MDB database (any version) you should use the correct provider MS JET 4.0:
Code:
Dim sDBPath as String
Set cnn = New ADODB.Connection
sDBPath = "C:\save space\workspace\funds\TIPS\test.mdb"
cnn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath
cnn.Open ' I suggest to read settings for ADODB.Connection Open method
Also, you don't say nothing about Windows version, therefore check the ADO version in you computer:
ADO 2.5 version (or greater) must be already installed in your computer.
However, if you use XP or Vista 2.8 is already shipped as default.
HTH
-
thanks.
Caio- that fixed the MSAccess side, and ultimately helped me figure out the problem for the SQL Server side too. thanks gibra. Liam O'Connell
-
 Originally Posted by liam_oconnell
Caio- that fixed the MSAccess side, and ultimately helped me figure out the problem for the SQL Server side too. thanks gibra. Liam O'Connell
The only change is related to a database connection string.
For all databases connection strings see here:
www.connectionstrings.com
HTH
-
Potentially the need for installing MDAC or some ODBC information could be necessary as well as the correct connection string.
Similar Threads
-
By nikosgeo in forum .NET
Replies: 16
Last Post: 08-22-2007, 06:31 AM
-
By rdmaday in forum Database
Replies: 6
Last Post: 08-20-2007, 06:13 PM
-
Replies: 1
Last Post: 07-28-2007, 04:16 PM
-
By Singer Wyatt in forum Java
Replies: 0
Last Post: 12-11-2001, 01:01 PM
-
By Jeff in forum VB Classic
Replies: 0
Last Post: 01-29-2001, 03:03 AM
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Development Centers
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center
|