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

Threaded View

  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

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