Solved

ADO and Querydef

Posted on 2014-04-04
6
735 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

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)

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

758 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