ADO and Querydef

I have this code which I use to connect to my server database from my PC from Access 2010 form.
I can access table pull recordset from it and it works fine.
I Entered some info on form fields save it in Product table  on server as new record and it works fine.
Now I would like to create a query in my Access database from the same product table.  I want to use this query  as data source for my Access subform.
I am usin ADO and I am not sure how to manipulate query definition (grydef) in ADO.
As I can access this table on the server and pull recordset from it is it possible somehow to switch this recordset to query an have it in my access database.
Below is my code for getting recordset  data.


'Make connection to server database and pull recordsets
    Dim cnn As ADODB.Connection
    Dim rstExp As ADODB.Recordset


    Set cnn = New ADODB.Connection
    Set rstExp = New ADODB.Recordset
   
    '*************
    cnn.Open "Provider=SQLOLEDB.1;" & _
        "Integrated Security=SSPI;" & _
        "Persist Security Info=True;" & _
        "Initial Catalog=PructionDB;" & _
        "Data Source=MM-LLL-PRO\ProdDB;" & _
        "Use Procedure for Prepare=1;" & _
        "Auto Translate=True;" & _
        "Packet Size=4096;" & _
        "Workstation ID=AAAA-NNN1;" & _
        "Use Encryption for Data=False;" & _
        "Tag with column collation when possible=False"



    rstExp.Open "Select * from ProSchema.Product", cnn, adOpenDynamic, adLockOptimistic
TarasAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
BitsqueezerConnect With a Mentor Commented:
Hi,

you do not need to specify a RecordSource property to bind a form with ADO. If you already have a working filled recordset with ADO (let's say "rs") then simply assign that to the Recordset property of the form. If you do that in the Form_Current event of the main Form and the subform container is named "sf" then it would look like this:

Private Sub Form_Current()
    Set Me.sf.Form.Recordset = rs ' Assuming that rs is module global, otherwise you could also build it here
End Sub

Open in new window


Leave any link field and the RecordSource property blank.

The trick is that a form in Access can use DAO and ADO recordsets in the same way.

Cheers,

Christian
0
 
DultonConnect With a Mentor Commented:
External Data -> More -> ODBC DataSource

->Link to the data soruce

->Machine Data Source
-> new
Scroll down until you see SQL Server (If you know your specific sql product, you can select one of the native clients that'll be a little more efficient). Otherwise use the generic SQL Server one, click next.
Name ->ProdDb

Description ->
Server -> MM-LL-PRO\ProdDb
->Next

-Windows authentication

->Next

-Default Database -> PructionDb   (Spelled how your Initial Catalog above is)


->Next
->Finish
->Test Data Source.... ->Ok ->Ok

------
now that the sql data source has been created on your machine, select that machine data source you just created (by name) and hit OK.....

scroll down until you find the object you're looking for ProSchema.Product

select it. and hit ok.  if the table has a unique identifier and you wish to tell access of it, do so on the next screen... after clicking ok, this dataset will appear as a linked table in your access file.


--edited for spelling correction.
0
 
TarasAuthor Commented:
I got this error message on New.
“You are logged on with non-Administrative privileges. System DSN could not be created or modified.

As this access database will be used on users PC that do not have admin role that DNS machine option is not the best.
I was able to access database and table through connection already.
Why I need now to set up machine DNS ?
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
One question would be why create an stored query in Access if you're working with unbound forms? Just open a recordset and do what you need with it. If you need to work with local tables, consider using a temporary table to store server-based information on a session basis.

DAO is always available in Access, so if you're comfortable using DAO with QueryDefs, then just use that.
0
 
TarasAuthor Commented:
Scott
To explain a little bit more.
I am entering products that a customer bought.
On the main form I pull data about the customer and then allocate preset product to him.
I am entering this info on main form and saving it to table and this is ok with existing connection and recordset option.

After entry is done I would like to put a sub form on main form  to show overview  of all product for that customer and it will be refreshed after each entry on main form.
However to show subform records (as spreadsheet or continual form type) with   all products for the customer I need query as source.
You are suggesting to use ADO recordset as source for spreadsheet or continues type of subform?
How?
0
 
TarasAuthor Commented:
Thanks a lot
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.