CODEWITHSUNDEEP
sqlms-accessvba
JohnMunich
JohnMunich

Reputation: 631

How to view a recordset in an access table by means of vba?

With help of the embedded access vb editor i've written a small code to analyse the field values of my database, and want to finally view the recordsets in a table inside the opened access. As a newbie i can only use Debug.Print to display the field names. Could anyone of you tell me with which statements/commands i can execute my SQL String in order to view the result recordset with values?

Debug.Print FinalSQLString

Upvotes: 2

Views: 34035

Answers (2)

iDevlop
iDevlop

Reputation: 25252

As far as I know, there is no way to display a datasheet containing a VBA instance of a recordset. If the source of your recordset is strQSL, you could however create a table with your results, and open that one, or more elegantly, create queryDef and open it: 

Sub ShowQd(strQdName As String, strSql As String)
'creates queryDef and display it in a datasheet'
    Dim qd As DAO.QueryDef

    Set qd = CurrentDb.CreateQueryDef(strQdName)
    With qd
        .ReturnsRecords = True
        .SQL = strSql
    End With
    DoCmd.OpenQuery strQdName
End Sub

If you focus on displaying things, you could also put a ListBox in a form, set its Number of columns to the number of fields returned by your query (qd.Fields.Count), and set your strSql as the RowSource of the ListBox. AND... if you put all your related code in that form, you now have a form that you can import in any db to quickly display what you want :)
Good luck !

Upvotes: 3

rskar
rskar

Reputation: 4657

Here is the basic recipe:

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM myTable")

Know that you are using Jet Data Access Objects (DAO) with Access - google that for details.

Expression (rs.BOF and rs.EOF) = True indicates there were no rows.

Use rs.MoveFirst, rs.MoveNext to go to the first and next rows. Test rs.EOF after rs.MoveNext; when True, last row was already processed.

rs(FieldName) returns the value of the column named FieldName (a string expression).

rs(1) returns the value of the second column.

When done, rs.Close.

There is no way to hand Access the RecordSet and have it displayed in a Datasheet view. Instead, you will have to create a QueryDef object and use it to perform the query and display the Datasheet view of the results:

Dim qd As QueryDef

On Error Resume Next
CurrentDb.QueryDefs.Delete "temp"
On Error GoTo 0

Set qd = db.CreateQueryDef("temp", "SELECT * FROM myTable")

DoCmd.OpenQuery "temp", acViewNormal, acEdit

Upvotes: 7

Related Questions