Solved

ADO and Querydef

Posted on 2014-04-04
6
737 Views
Last Modified: 2014-04-07
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
0
Comment
Question by:Taras
6 Comments
 
LVL 6

Assisted Solution

by:Dulton
Dulton earned 250 total points
ID: 39978879
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
 

Author Comment

by:Taras
ID: 39978911
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
 
LVL 84
ID: 39979923
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Taras
ID: 39980158
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
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 250 total points
ID: 39980974
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
 

Author Closing Comment

by:Taras
ID: 39983327
Thanks a lot
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now