DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: database connection via adodb/adox in excel

  1. #1
    Join Date
    Apr 2009

    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.
    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.Provider = "SQLOLEDB"
    'cnn.ConnectionString = "Driver={SQL Server};" & _
    '        "server=FIT-OCONNLC-P01\SQLEXPRESS; database=HistoricalData; 
    Trusted_Connection=yes; "
    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
    Set cat.ActiveConnection = Nothing
    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

  2. #2
    Join Date
    Mar 2009
    Italy - Breganze (VI)
    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:

    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.


  3. #3
    Join Date
    Apr 2009


    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

  4. #4
    Join Date
    Mar 2009
    Italy - Breganze (VI)
    Quote Originally Posted by liam_oconnell View Post
    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:


  5. #5
    Join Date
    Apr 2007
    Sterling Heights, Michigan
    Potentially the need for installing MDAC or some ODBC information could be necessary as well as the correct connection string.

Similar Threads

  1. Replies: 16
    Last Post: 08-22-2007, 06:31 AM
  2. VB6 connection to Progress Database
    By rdmaday in forum Database
    Replies: 6
    Last Post: 08-20-2007, 06:13 PM
  3. dynamic database connection
    By lisav in forum .NET
    Replies: 1
    Last Post: 07-28-2007, 04:16 PM
  4. Replies: 0
    Last Post: 12-11-2001, 01:01 PM
  5. 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
HTML5 Development Center
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center