CODEWITHSUNDEEP
ms-accessvbams-office
user748872
user748872

Reputation: 1

How to save a MS Access filtered table as a query in VBA

I need to be able to save the results of a filtered table in Microsoft Access(2010) as a query. Reports in Access are only dynamic if they are based off of a query. If I based the reports off of a the table itself, it wouldn't save any of the search terms/filter results. In the Access macro builder, the DoCmd.save only saves the current table, I need to be able to do a "save as" in VBA so I can save the filtered table as a query.

Thanks

Upvotes: 0

Views: 4611

Answers (1)

Thomas
Thomas

Reputation: 64635

You would need to build the SQL statement based on the Filter and OrderBy settings of the form.

Dim sSQL As String

sSQL = "Select ... From MyTable"

If Len(Me.Filter) > 0 Then
    sSQL = sSQL & " Where " & Me.Filter
End If

If Len(Me.OrderBy) > 0 Then
    sSQL = sSQL & " Order By " & Me.OrderBy
End If

Call DBEngine.Workspaces(0).Databases(0).CreateQueryDef("MyQueryName", sSQL)

Upvotes: 1

Related Questions