-
Jun 27th, 2013, 04:09 PM
#1
Thread Starter
New Member
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
-
Jul 26th, 2013, 03:44 PM
#2
Thread Starter
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|