System ODBC parameter in VBA code gives 3423 run-time error code

System ODBC parameter in VBA code gives 3423 run-time error code

Post by Donald Bridge » Fri, 13 Sep 2002 03:08:22

I have an Excel spreadsheet with VBA code to pull data from an Access
database to populate a listbox. When I hard code the location of the
database in the OpenDatabase parameters, it works. However, when I try
to set it as a ODBC connection, it returns a run-time error 3243 code.
The error states "You cannot use ODBC to import from, export to, or link
an external Microsoft Jet or ISAM database table to your database". I
have configured the Data Source as a System DSN pointing to the
database. I do not have a UserName or Password configured on the database.
The code that works is as follows:

Public MyCriteria As String, MyCriteria2 As String, MyCriteria3 As String

Dim MyDB As Database
Dim MySet As DAO.Recordset
Dim MySet2 As DAO.Recordset
Dim MySet3 As DAO.Recordset
Dim Bidsheet As Object
Dim MyObject As Object
Dim sqlStr As String
Dim sqlStr2 As String
Dim sqlStr3 As String
Dim rownum As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer

Private Sub UserForm_Initialize()
Set MyDB = OpenDatabase("c:\shared\access\equipment.mdb", False, False)
sqlStr = "SELECT DISTINCT manufacturer FROM equipment"
Set MySet = MyDB.OpenRecordset(sqlStr)
For i = 1 To MySet.RecordCount
EquipSelector.ListBox1.AddItem MySet.Fields("manufacturer").Value
Next i
End Sub

When I make the following change:

Private Sub UserForm_Initialize()
Set MyDB = OpenDatabase("", False, False, "ODBC;DSN=equipment")

I get the run-time error.

So why is it that hard coding works but ODBC does not?

I need to be able use ODBC for the following reason: most of the
connections will be at the office connect to the LAN but some will be in
the field on laptops not connected. The desktops will have their System
DNS modified to point to network located database on the server and the
laptops will have their System DNS modified to point to the local copy
of the Access database on their C: drive.

Any guidance is greatly appreciated.