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

[HealthyB1]

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]

you perhaps need a 64-bit odbc driver

ignore that.

can you not just link the tables as access tables?

 

[HealthyB1]

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