how to have two ADODB Connection in Access VBA

[luzz]

Hello everyone,

I would like to ask on how to have two ADODB Connection in Access, so that i will be able to retrieve two recordset from my table and display it on my form.

I am able to retrieve the recordset for my rsMax. And now i would like to add in the runningno database to retrieve the recordset to display on my form.
I tried adding in the runningno as a recordset as shown in my code below, and i get error"The connection cannot be used to perform this operation. It is either closed or invalid in this context"

What can i do to resolve this problem? Thankyou.

Below is the code that i have try:

Set con = CurrentProject.connection
Set rsMax = New ADODB.Recordset
'retrieve data from Max
rsMax.Open "SELECT PO,Content,Reference,elDate,Fabrication,Width,Color,Weight,SUM(Qty)as SumofQty," & _
"SUM(Sqty) as [SumofSqty from Max " & _
"GROUP BY Fabrication,Width,Color,DelDate,PO,Weight,Content,Reference;", con, adOpenDynamic, adLockOptimistic

Code for runningNo:
Dim connection As ADODB.connection
Dim rsRunningNo As ADODB.Recordset
Set connection = CurrentProject.connection
Set rsRunningNo = New ADODB.Recordset
'retrieve data from RunningNo
rsRunningNpen "SELECT CompanyCode,YearCode,PONumber from RunningNo"

 

[Ranman256]

you DONT need ADO connection in access.
You are already in the database, and already connected.
Just run a query.

ADO is for accessing the tables OUTSIDE the database, like in excel, or Word.

 

[luzz]

you DONT need ADO connection in access.
You are already in the database, and already connected.
Just run a query.

ADO is for accessing the tables OUTSIDE the database, like in excel, or Word.

Hi, what do you mean by run a query? do you mean run the query through query design?

 

[Minty]

I'm going to step in here and point you back here https://www.access-programmers.co.uk/forums/showpost.php?p=1545278&postcount=5
Particularly about doing some basic Access tutorials. Get to grips with the basics please.