Solved

ADO and Querydef

Posted on 2014-04-04
6
748 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

820 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