Coded Query for MS Access 2007 VBScript

stimbadia1 — Mon, 28 Mar 2011 14:07:07 GMT

Dear InfoDev experts,

This forum has been a lifesaver! It is super-awesomeness and thank you to all who have contributed :)

Infopath/Access 2007 and lang: VbScript.

I want to be able to query the db to present only a selected amount of data on a different view. This will all happen when a user clicks a button.

After all the research/ surfing, I understand there are two methods - either create an ADODB connection or use existing main data connection. I'm failing at both! :( Please help.

1) Creating a new connection - below is the code I'm using.

Sub CTRL38_7_OnClick(eventObj)
' Connection String required to connect to MS Access database
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Z:\New folder structure\Patient Safety Team work\Patient Safety Walkabouts\PSW Database\Patient Safety Test Base.accdb;"

Dim cn
Dim cmd

' SQL statement to run
sql = "SELECT RelationshipKey, UnitDes, ClinicalGroup, Specialty, ExactLocation, Date of visit, Next walkabout due FROM PSWVisitsUnique ORDER BY UnitDes"

' Create ADO Connection/Command objects
set cn = createobject("ADODB.Connection")
set cmd = createobject("ADODB.Command")

' Open connection
cn.open connectionString
' Associate connection object with command object
cmd.ActiveConnection = cn
' Set the SQL statement of the command object
cmd.CommandText = sql

' Execute query
set rs = cmd.execute

' Close Connection
cn.Close

End Sub

Here it tells me "unknown exception" at set rs = cmd.exectue

2) Use existing main connection: (I'd like to use this method):

Dim conn

Set conn = XDocument.DataObjects("/dfs:myFields").adoQueryAdapter

adoQueryAdapter.Command = "SELECT RelationshipKey, UnitDes, ClinicalGroup, Specialty, ExactLocation, Date of visit, Next walkabout due FROM PSWVisitsUnique ORDER BY UnitDes"

adoDataAdapter.Query()

Here, it does not recognise my main connection. I've tried several different names - what is the best approach, please please advise?

Microsoft Access

Coded Query for MS Access 2007 VBScript