database connection via adodb/adox in excel


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
    Posts
    1

    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 01:00 PM. Reason: Added Code Tags

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

  3. #3
    Join Date
    Apr 2009
    Posts
    1

    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

  4. #4
    Join Date
    Mar 2009
    Location
    Italy - Breganze (VI)
    Posts
    120
    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:
    www.connectionstrings.com

    HTH

  5. #5
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Potentially the need for installing MDAC or some ODBC information could be necessary as well as the correct connection string.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

Similar Threads

  1. Replies: 16
    Last Post: 08-22-2007, 07:31 AM
  2. VB6 connection to Progress Database
    By rdmaday in forum Database
    Replies: 6
    Last Post: 08-20-2007, 07:13 PM
  3. dynamic database connection
    By lisav in forum .NET
    Replies: 1
    Last Post: 07-28-2007, 05: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
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

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