Cannot use ODBC to import from, export to, or link to an external Microsoft Access o (1 Viewer)

HealthyB1

Registered User.
Local time
Today, 12:10
Joined
Jul 21, 2013
Messages
82
G'day,

I am trying to create an ODBC link to a copy of the Northwind data base.

I am running Win7 on a 64 bit operating system but am keeping office running as 32 bit for Access, Excel , Visio etc due to the fact that I have a lot of old apps tied to them.

When I attempted initially to create a new system DSN ODBC connection it only allowed me to use SQL drivers. (No plurry good) So after some searching I found out that I could use a file in C:\Windows\SysWOW64 called "odbcad32.exe" which has allowed me to view /use the total list of drivers to import data.
So I can set up a DNS ODBC named Northwind1 and I can access the database without problems using Excel.
However if I try to use the same ODBC link in Access to connect to Northwind1 I get the following error message:
"You cannot use ODBC to import from export to, or link an external ?Microsoft Access or ISAM database table to your database"

What am I missing? This is driving me crazy.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 03:40
Joined
Sep 12, 2006
Messages
14,889
you perhaps need a 64-bit odbc driver

ignore that.

can you not just link the tables as access tables?
 

HealthyB1

Registered User.
Local time
Today, 12:10
Joined
Jul 21, 2013
Messages
82
Hi Dave,

Yes that seems to work ok. Many thanks.

I am trying to get my head around recordset clone and was attempting to use the following VBA code that I obtained from the web.
____________________________________________________
Option Compare Database
Sub BookmarkX()

Dim dbsNorthwind As Database
Dim rstCategories As Recordset
Dim strMessage As String
Dim intCommand As Integer
Dim varBookmark As Variant

10 Set dbsNorthwind = Northwind.mdb
20 Set rstCategories = _
dbsNorthwind.OpenRecordset("Categories", dbOpenSnapshot)

30 With rstCategories

40 If .Bookmarkable = False Then
50 Debug.Print "Recordset is not Bookmarkable!"
60 Else
' Populate Recordset.
70 .MoveLast
80 .MoveFirst

90 Do While True
' Show information about current record and get
' user input.
100 strMessage = "Category: " & !CategoryName & _
" (record " & (.AbsolutePosition + 1) & _
" of " & .RecordCount & ")" & vbCr & _
"Enter command:" & vbCr & _
"[1 - next / 2 - previous /" & vbCr & _
"3 - set bookmark / 4 - go to bookmark]"
110 intCommand = Val(InputBox(strMessage))

120 Select Case intCommand
' Move forward or backward, trapping for BOF
' or EOF.
Case 1
130 .MoveNext
140 If .EOF Then .MoveLast
150 Case 2
160 .MovePrevious
170 If .BOF Then .MoveFirst

' Store the bookmark of the current record.
180 Case 3
190 varBookmark = .Bookmark

' Go to the record indicated by the stored
' bookmark.
200 Case 4
210 If IsEmpty(varBookmark) Then
220 MsgBox "No Bookmark set!"
230 Else
240 .Bookmark = varBookmark
250 End If

260 Case Else
270 Exit Do
280 End Select

290 Loop

300 End If

310 .Close
320 End With

330 dbsNorthwind.Close

End Sub

___________________________________________________________

However it stops at line 10 with the following message

Run-Time Error '424':
Object Required

I am unsure if I am getting the error because it can't find Northwind.mdb as I have it in the wrong directory or because the ODBC link can't be defined to point to it?
I assumed it was the latter hence the original question, as defining an ODBC link called Northwind should point it to the right location.

Cheers,

Bob
 

Users who are viewing this thread

Top Bottom