ADO and Querydef
Posted on 2014-04-04
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