DAO/ADO Microsoft Excel 2007 Help-VBForums
Results 1 to 2 of 2

Thread: DAO/ADO Microsoft Excel 2007 Help

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2013
    Posts
    2

    DAO/ADO Microsoft Excel 2007 Help

    Hello,

    I'm majoring in chemical engineering, so sorry if I get any information wrong. I know a bit about computers and coding, but I know absolutely nothing about databases.

    I am using DAO code (pretty sure) to connect to a SQL database from my Microsoft Excel 2007 VBA program. I think the data base is Microsoft Access but I'm not sure. The data is taken from a controls system in a chemical plant.

    This code was written ages ago to pull data from the control system and put it a spreadsheet for engineers to look at. It works fine on a windows XP machine, but we need to upgrade to W7. When I run the code I get:

    Code:
    Runtime error 3633 cannot load MSRDO20.dll
    I tried adding the .dll file and referencing it, but had no luck.
    From what I've read DAO is obsolete and I will need to update the code to use ADO. Do you know an easy work around updating all of the code or a reference on how to transition from DAO to ADO?
    I can post more code if needed.

    Here's my code:
    VB Code:
    Code:
    Function get_autoload_data() As Boolean
    Dim dbsLink As Database
    Dim rstShipmentData As Recordset
    Dim wrkODBC As Workspace
    Dim SQLQuery As String
    Dim Day_Start As Date
    Dim Day_End As Date
    Dim dead_ans As Integer
    
    
    Workbooks("fakename.xls").Sheets("Autoload Data").Range("a6").Value = "Not Available"
    Workbooks("fakename.xls").Sheets("Autoload Data").Range("a10").Value = "Not Available"
    Workbooks("fakename.xls").Sheets("Autoload Data").Range("a14").Value = "Not Available"
    
    Day_Start = Workbooks("fakename.xls").Sheets("Autoload Data").Range("b2").Value
    Day_End = Workbooks("fakename.xls").Sheets("Autoload Data").Range("b3").Value
    
    ' Open MS Jet Workspace
    Set wrkODBC = CreateWorkspace("ODBCWorkspace", "admin", "", dbUseODBC)
        
    ' Open a database
    Err.Clear
    On Error Resume Next
    Set dbsLink = wrkODBC.OpenDatabase("WINNIE", dbDriverNoPrompt, True, _
                        "ODBC;DATABASE=link;UID=prodrept;PWD=Report08;DSN=WINNIE")
    Thank you,
    Alex

  2. #2

    Thread Starter
    New Member
    Join Date
    Jun 2013
    Posts
    2

    Re: DAO/ADO Microsoft Excel 2007 Help

    I've been reading more and figured out what my connection string is. Can anyone help me with updating the ODBC syntax to ADO?

    Alex

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.