Douglas,
You can use the Data Access Object (DAO) in Windows by simply referencing it
into your VBA project. ([Tools/References] then select "Microsoft DAO 3.x
Oject Library".)
This will allow you to use either an ODBC or Jet Database Engine connection
to insert/update/select data from the MDB file directly. You may have to
learn a little about the DAO, but that probably would be time well spent if
you plan to work with databases in the future--and a ton of information is
available on it.
Here is some code that uses the Jet Database Engine. I pulled it from an old
example I wrote, and it probably will not work in it's current state.
(paths, filenames etc.) but it shows how to open you MDB file using the DAO.
>>>>>>>>>>>>>>>>>>>>>
Option Explicit
Public gws As Workspace
Public gdb As Database
Public gstrDBName As String
Public Function OpenPartsDatabase(strDatabasePath As String) As Boolean
OpenPartsDatabase = False
Dim strPswd As String
On Error GoTo UnknownError
If IsFileFound(strDatabasePath) Then
' Sets password
strPswd = ""
' sets and opens the global workspace
Set gws = CreateWorkspace("PartsWorkspace", "Admin", "", dbUseJet)
' sets and opens the global database
Set gdb = gws.OpenDatabase(strDatabasePath, False, False, ";pwd=" &
strPswd)
Else
GoTo UnknownError
End If
OpenPartsDatabase = True
UnknownError:
End Function
Public Function IsFileFound(strFullFileName As String) As Boolean
IsFileFound = False
On Error GoTo TheFileWasntFound
If strFullFileName = "" Then
Exit Function
End If
If Dir(strFullFileName, vbNormal) <> "" Then
IsFileFound = True
Exit Function
End If
TheFileWasntFound:
End Function
Function drawMyCube()
ThisDrawing.Regen False
If OpenPartsDatabase("C:\DrawBox.mdb") Then
frmBox.GetPartsList
frmBox.Show
Else
MsgBox "The database coudn't be found.", vbOKOnly, "Error opening
database"
End If
End Function
Sub Test()
drawMyCube
End Sub
<<<<<<<<<<<<<<<<<<<<<
This code retrieves data from the MDB file.
>>>>>>>>>>>>>>>>>>>>>
Function GetPartsList() As Boolean
GetPartsList = False
Dim ors As Recordset
Dim strSQL As String
On Error GoTo UnknownError
strSQL = "SELECT DISTINCT PartNumber, Description "
strSQL = strSQL & "FROM Parts "
strSQL = strSQL & "ORDER BY PartNumber "
Set ors = gdb.OpenRecordset(strSQL, dbOpenSnapshot)
If ors.RecordCount Then
While Not ors.EOF
lstParts.AddItem (ors!PartNumber)
lstParts.List(lstParts.ListCount - 1, 0) = ors!PartNumber
lstParts.List(lstParts.ListCount - 1, 1) = ors!Description
ors.MoveNext
Wend
GetPartsList = True
lstParts.ListIndex = 0
End If
ors.Close
UnknownError:
End Function
<<<<<<<<<<<<<<<<<<<<<
This code updates (puts) data in the MDB file. (You would probably use an
"INSERT" query here.)
>>>>>>>>>>>>>>>>>>>>>
Function UpdateData() As Boolean
UpdateData = False
Dim strSQL As String
On Error GoTo UnknownError
strSQL = "UPDATE Parts "
strSQL = strSQL & "SET Length = " & txtLength & ", "
strSQL = strSQL & "Width = " & txtWidth & ", "
strSQL = strSQL & "Height = " & txtHeight & " "
strSQL = strSQL & "WHERE PartNumber = """ &
lstParts.List(lstParts.ListIndex, 0) & """"
gdb.Execute strSQL
UpdateData = True
UnknownError:
End Function
<<<<<<<<<<<<<<<<<<<<<
Notice that I use ANSI SQL for all my read/write opperations into the MDB
file. This will allow you to easily move this code to use a ODBC record
source if you ever want to. (A good habit.)
All you have to do is change this:
Set gws = CreateWorkspace("PartsWorkspace", "Admin", "", dbUseJet)
To this:
Set gws = CreateWorkspace("PartsWorkspace", "Admin", "", dbUseODBC)
Then you might have to change the connection string (as the following line
would not be valid for a SQL Server or Oracle connection:
Set gdb = gws.OpenDatabase(strDatabasePath, False, False, ";pwd=" &
strPswd)
I hope this helps!
Until later,
Jeremy McMahan
Premier Product Support
WW Support & Services, Autodesk
Discussion Q&A: http://www.autodesk.com/discussion
"DouglasPoston" wrote in message
news:ef04111.0@WebX.SaUCah8kaAW...
> Whats the easiset way for Access '97 and AutoCAD 2000 to interact as far
as Extracting information out of AutoCAD drawings and having it show up in
Access.
> I know how to control ACAD in VB/VBA using attributes and so forth. But is
there an easier way like ODBC or something.
