Import Data from Access Database in a Workgroup

[David]

H!,

Hope someone can help me, I've been trying to figure
this out all day. I have a Database on this Path:
C:\AssetTracker\Access 2000
\MMH_CIL_RevN_Cnvrtd.mdb /Wrkgrp \\owgn1mmd\mmh_pm2
\Asset_Tracker\CIL2000Compacted.mdw /Nostartup.

I want to import the data from an Access 2000 database
table to an Excel 2000 worksheet. The database is
password protected and part of a workgroup. I've tried
the Wizard, Modifying the .dsn file & attempted to do it
by creating a VB function. No dice.

Thanx

 

[Heapy]

Use the SystemDB property of the DBEngine Object.

e.g.

Sub TestCxn()

Dim ws As Workspace
Dim db As Database

DBEngine.SystemDB = "\\owgn1mmd\mmh_pm2\Asset_Tracker\CIL2000Compacted.mdw"
Set ws = CreateWorkspace("", "userID", "password")
Set db = ws.OpenDatabase("C:\AssetTracker\Access
2000\MMH_CIL_RevN_Cnvrtd.mdb ", True)
Debug.Print db.TableDefs.Count

db.Close
ws.Close

End Sub


Replace userID and password with your credentials. If your access is
validated, the db's tablecount will be displayed in the immediate window. If
not, you'll get an error.

From here you can open whatever recordsets you need to get the data into xl
(as long as you have the appropriate permissions).

Cheers,
HWH