How do I make the standard Access functions work after a connection to an external database?-CodePudding

The VBA code for external connection has been given to my by our IT-department. I did not wrote it myself. I just made some adjustments to make it work for my database. I am not an expert. The function Clear_Analysis just deletes all records from tabel tbl_Analysis. The function Analysis_Check does work in all my other functions. I want to check if a table contains any records.

Function Analysis_Check()

    If DCount("*", "tbl_Analysis") = 0 Then

        MsgBox ("No Records!")

    End If
    
        If DCount("*", "tbl_Analysis") <> 0 Then

        MsgBox ("Success!")

    End If
    
End Function

But this function does not work anymore after a connection to an external SQL-database.

How do I make this work in the same function?

Function Analysis_LIMS()

    Dim objRec
    Dim objConn
    Dim cmdString
    Dim insertString

    Call Clear_Analysis
    
    Set objRec = CreateObject("ADODB.Recordset")
    Set objConn = CreateObject("ADODB.Connection")

    objConn.ConnectionString = "Provider=.....; "
    objConn.Open

    Set localDbConn = CreateObject("ADODB.Connection")
    localDbConn.ConnectionString = "...;Persist Security Info=False;"
    localDbConn.Open
    cmdString = "SELECT DISTINCT t.rapportnaam, t.norm FROM taken t"
    Set objRec = objConn.Execute(cmdString)

    Do While Not objRec.EOF
        insertString = "INSERT INTO tbl_Analysis ([Analysis], [Analysis_Norm]) VALUES ('" & objRec("rapportnaam") & "', '" & objRec("norm") & "')"
        localDbConn.Execute (insertString)
        objRec.MoveNext
    Loop

    Call Analysis_Check

    localDbConn.Close

I want to add the call function Analysis_Check at the end of function Analysis_LIMS

I also tried to make Dcount as a seperate function and call this function at the end of function Analysis_LIMS I can confirm that there are definately records in table tbl_Analysis. But for both methods the Dcount function gives 0 as answer. This is not the case.

If I make a button on the same form which calls function Analysis_Check, then function Analysis_Check does work. But after that, if I call function Analysis_Check through function Analysis_LIMS the result is that I get popup message "No Records", I click OK, then comes a second popup message "Success". If then I do the same thing again, but without using that temporary button it only gives the "No Records" aswer, but there are definately records in tbl_Analysis I have no idea what I am doing wrong or why Access behaves like this. I guess it has something to do with the recordset? Maybe set the recordset to CurrentDb? Any help would be appriciated.

CodePudding user response：

Probably a timing issue as the query calls will run in another context.

Try either with DoEvents:

Do While Not objRec.EOF
    insertString = "INSERT INTO tbl_Analysis ([Analysis], [Analysis_Norm]) VALUES ('" & objRec("rapportnaam") & "', '" & objRec("norm") & "')"
    localDbConn.Execute (insertString)
    objRec.MoveNext
Loop
DoEvents

or use DAO:

Dim Records As DAO.Recordset
Set Records = CurrentDb.OpenRecordset("Select * From tbl_Analysis", dbOpenDynaset, dbAppendOnly)
Do While Not objRec.EOF
    Records.AddNew
        Records!Analysis.Value = objRec("rapportnaam").Value
        Records!Analysis_Norm.Value = objRec("norm").Value
    Records.Update
    objRec.MoveNext
Loop
Records.Close