CODEWITHSUNDEEP
excelms-accessvba
ASH
ASH

Reputation: 20362

Weirdness with VBA in Excel and Access

I have the following code. 

Sub Run_Queries_In_Access()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim acc As Object
Dim db As Object
Dim r As Long
Dim strDatabasePath As String

Set conn = New ADODB.Connection
'Set conn = CreateObject("Access.Application")
strDatabasePath = ThisWorkbook.Path & "\AI.mdb"
conn.ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & strDatabasePath & ";" & _
    "Jet OLEDB:Database Password=xxx"
conn.Open

Set acc = New Access.Application
'acc.Visible = True
Set db = CurrentDb()


Application.Wait DateAdd("s", 2, Now) 'Wait for 2 seconds
    acc.DoCmd.SetWarnings False
    acc.DoCmd.OpenQuery "qry_Delete_ALLL"
    Dim qry As Object
    Set qry = db.QueryDefs("qry_HIST")
    qry.Parameters(0) = Range("ASOFDATE")
    qry.Execute

Application.Wait DateAdd("s", 2, Now) ' Wait for 2 seconds
    Set qry = db.QueryDefs("qry_LIMIT_HIST") ' Run Append Query
    qry.Parameters(0) = Range("ASOFDATE")
    qry.Execute
    acc.DoCmd.SetWarnings True

    ' SELECT ALL DATA
    Set qry = db.QueryDefs("qry_TBL_DATA")
    Worksheets("Impact Analysis").Range("A11:L5000").Clear
    Set daoRcd = qry.OpenRecordset
    ThisWorkbook.Worksheets("Impact Analysis").Range("A11").CopyFromRecordset daoRcd

conn.Close
Set conn = Nothing


End Sub

I have references set as follows.

enter image description here

If I step through the code by hitting F8 over and over, everything works fine. If I fire the code by a button click event, I get the following error message: 'run time error 462: the remote server machine does not exist or is not available'

When I check the code, I see that the db is actually set to nothing!!

enter image description here

enter image description here

I'm guessing that it has to do with one of my references, or the way I declared one of my variables, but that's just a guess. I don't know for sure. Any ideas would be very welcome!! Thanks!!

Upvotes: 0

Views: 405

Answers (1)

Johnny Bones
Johnny Bones

Reputation: 8414

There's nothing wrong with your code. At the point where you have a break (i.e., the line you have highlighted), the variable "db" is not set to anything. Step to the next line and then check the value of db and it should be set to whatever the database is named.

As for the actual issue at hand, you may want to check this post:

Running into Error 462: The remote server machine does not exist when working with WORD via Excel VBA

Even though it deals with Word, it may give you a clue as to why it's happening for you.

There's also this post which may help as well:

"Run-time error 462 : The remote server machine does not exist or is unavailable" when running VBA code a second time

Since you didn't post your entire code, it's hard to tell what the issue might be. If either of these helps, please come back and post a comment under here that explains what you did and how you fixed it, just in case it happens to anyone else.

Upvotes: 1

Related Questions