How to set the name of the DAO.Database

[GraeagleBill]

I have a form with modules that save new user subscription profile data. Additionally, with a double-click action on a SAVE command button, the user wants the same profile information registered in a parallel database. (Parallel meaning the app services multiple DBs)

What I've never had the occasion to do is set the name of the parallel DB in code as you see in my feeble attempt below, so a little guidance here would be most helpful. In the code below, see the statement where I want to open a DAO.Recordset in another DB. The statement is wrong, but a search on methods didn't reveal how to do that?

Private Sub cmdSAVE_DblClick(Cancel As Integer)
If strSDMA = "Menus" Then bolSaveAct = True       'Tell the SAVE code we need to also save the new subscriber in Activities.
End Sub


Private Sub cmdSAVE_Click()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'  Okay, everything needed to enter a new record has been satisfied.  If the user action was a double-click on
'  the SAVE command button, the intent is that the same individual be subscribed in our parallel DB for Activities.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim rsSav As DAO.Recordset
Dim dbs As DAO.Database
Dim rsActSub As DAO.Recordset


If bolNewSubscriber Then
    Set rsSav = DBEngine(0)(0).OpenRecordset("QSubscribers")
    
        rsSav.AddNew
        rsSav!LASTNAME = Me.tbLASTNAME
        rsSav!FIRSTNAME = Me.tbFIRSTNAME
        rsSav!AptNum = Me.tbAptNum
        rsSav!Spouse = Me.tbSpouse
        rsSav!Cell = Me.tbCell
        rsSav!LandLine = Me.tbLL
        rsSav!ImageName = Me.tbImageName
        rsSav!EMA = Me.tbEMA
        rsSav!Notes = Me.tbNotes
        rsSav.Update
    
    rsSav.Close
    Set rsSav = Nothing
    
    If bolSaveAct = True Then
        Set dbs = "c:\SDMA\SDMA-ActData.accdb"        '<<<<<<< Put the name in SETTINGS after we learn how to do this
        
        'Open the Subscriber table in the Activities DB
        Set rsActSub = dbs.OpenRecordset("tblSubscribers", dbOpenTable)
        
'<SNIP>
        
    End If
End If






DoCmd.Close acForm, "frmProfile", acSaveYes


End Sub

[Welshgasman]

Why not ask an AI?
ChatGPT offers

Sub CopyDataBetweenAccessDbs()
    Dim dbSource As DAO.Database
    Dim dbDest As DAO.Database
    Dim rsSource As DAO.Recordset
    Dim rsDest As DAO.Recordset
    Dim dbPath As String

    dbPath = "C:\Path\To\Source.accdb"

    Set dbDest = CurrentDb
    Set dbSource = DBEngine.OpenDatabase(dbPath)

    Set rsSource = dbSource.OpenRecordset("SourceTable")
    Set rsDest = dbDest.OpenRecordset("DestinationTable")

    Do Until rsSource.EOF
        rsDest.AddNew
        rsDest!Field1 = rsSource!Field1
        rsDest!Field2 = rsSource!Field2
        rsDest.Update
        rsSource.MoveNext
    Loop

    rsSource.Close
    rsDest.Close
    dbSource.Close
End Sub

With a query

INSERT INTO DestinationTable (Field1, Field2)
IN 'C:\Path\To\Destination.accdb'
SELECT Field1, Field2
FROM SourceTable;

and of course there is always the linked table option?

[GraeagleBill]

Why not ask an AI?

I deserve that! That's the second time you've suggested I try ChatGPT for my searches.

Thanks,
Bill

[Welshgasman]

Well I am using them more and more in my replies, plus they supply code to start with.
Wish they existed when I was learning.
Would have saved me a lot of googling.

[GraeagleBill]

Okay, while I already knew how to do this, I thought I'd ask ChatGPT how to pass a DAO.Recordset to a Sub.
https://chatgpt.com/?utm_source=goog...IaAr8kEALw_wcB

For completions sake, here's the implemented code, though I haven't as yet moved the name of the alternate DB to its appropriate place in SETTINGS.

Private Sub cmdSAVE_Click()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'  Okay, everything needed to enter a new record has been satisfied.  If the user checked the option box to also add
'  the same individual the Activities DB, we simply open that DB and add the same records there.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim rsSav As DAO.Recordset
Dim dbActivities As DAO.Database
Dim rsActivities As DAO.Recordset
Dim dbPath As String
    
If bolNewSubscriber Then
    Set rsSav = DBEngine(0)(0).OpenRecordset("QSubscribers")
    Call AddToRS(rsSav)
    
    rsSav.Close
    Set rsSav = Nothing


    If chkActSave = True Then
        dbPath = "c:\SDMA\SDMA-ActData.accdb"
    
        Set dbActivities = DBEngine.OpenDatabase(dbPath)
        Set rsActivities = dbActivities.OpenRecordset("tblSubscribers")
        
        Call AddToRS(rsActivities)


        rsActivities.Close
        dbActivities.Close
        
    End If
End If


DoCmd.Close acForm, "frmProfile", acSaveYes


End Sub


Private Sub AddToRS(rs As DAO.Recordset)
    rs.AddNew
    rs!LASTNAME = Me.tbLASTNAME
    rs!FIRSTNAME = Me.tbFIRSTNAME
    rs!AptNum = Me.tbAptNum
    rs!Spouse = Me.tbSpouse
    rs!Cell = Me.tbCell
    rs!LandLine = Me.tbLL
    rs!ImageName = Me.tbImageName
    rs!EMA = Me.tbEMA
    rs!Notes = Me.tbNotes
    rs.Update
End Sub

[madpiet]

you should generally close and destroy (Set = nothing) all of the object variables you create. Just avoids the "too many objects open" errors and junk in memory that you don't need.

Call AddToRS(rsActivities)


        rsActivities.Close
        dbActivities.Close

        '--cleanup
        set rsActivities = Nothing
        set dbActivities = Nothing
        
    End If
End If

[Welshgasman]

Like Google, I use ChatGPT as a start.
Up to you to amend as required.?

So far, for all the people I have helped, the code looks good?
However it is up to them to test? After all, they should have to do some of the work themselves, otherwise how do they learn?
The number of times times I have seen someone post some code and the member just comes it as is, with no idea, is too many to count. :-(

[GraeagleBill]

Yes, I had already done that right after post #5 when I spotted that I hadn't yet left a clean house. I think it was Paul that cautioned me about that years ago.