Microsoft Access run-time error '462'

[spencer.67965]

Hello all,

I am trying my hand trying to accessing data from a "Microsoft Access 2000" database from AutoCAD 2005. My purpose is to pull some information from a specified table and then return the value to AutoCAD as a Variable for further manipulation. It works great the first go around then I get this error:

Run-time error '462':
The remote server machine does not exist or is unavailable.

Does anyone know what could be causing this to happen? Also if I exit out of AutoCAD and then get back into AutoCAD and run the macro again it works fine the first go around then it give the same error.

Here is my code thus far.

-------------------------------

Public Sub TestAccess()

Dim acApp As Access.Application
Dim AcuteDatabase
Dim ID
Dim ClientName

AcuteDatabase = "y:/test.mdb"
ID = 1

Const ERR_APP_NOTRUNNING As Long = 429

On Error Resume Next

' Attempt to reference running instance of Access.
Set acApp = GetObject(AcuteDatabase, "Access.Application")

' If Access isn't running, create a new instance.
If Err = ERR_APP_NOTRUNNING Then
Set acApp = New Access.Application
End If

ClientName = DLookup("[field1]", "[table1]", "[id]=1")

If Not acApp.UserControl Then
acApp.Quit
Set acApp = Nothing

End If

End Sub

-------------------------------

Let me know if any more clarification is required.

Thanks in advance

[jose.hernandez]

Most likely your database is not open when dlookup is executed, A better approach is to query the database using DAO.

Here is a sample...

BEFORE you execute this code, in the VBA editor goto Tools, References and set a reference to "Microsoft DAO x.x Object Library".

<-- Start Sample Code -->
Option Explicit

Private Const DB As String = "y:/test.mdb"

Sub Main_DataAccess()
On Error GoTo Proc_Error
Dim dbDAO As DAO.Database
Dim oRS As DAO.Recordset
Dim strSQL As String
Dim strClientName As String

Set dbDAO = DAO.OpenDatabase(DB, , True)

strSQL = "SELECT [field1] FROM [table1] WHERE [ID]=1"
Set oRS = dbDAO.OpenRecordset(strSQL, dbOpenSnapshot)

If oRS.RecordCount = 1 Then
strClientName = oRS.Fields("field1").Value
Else
MsgBox "Multiple Records found!", vbCritical
End If

Proc_Exit:
If Not oRS Is Nothing Then Set oRS = Nothing
If Not dbDAO Is Nothing Then Set dbDAO = Nothing
Exit Sub
Proc_Error:
MsgBox Err.Description
Resume Proc_Exit
End Sub
<-- End Sample Code -->

One more thing always use "Option Explicit" add it to the top of your modules or select "Require Variable Declaration" in VBA editor, Tools, Options.

- Jose

[spencer.67965]

Thanks i'll give it a try.

[spencer.67965]

Worked Like a charm.

Thanks again.