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

Question

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

The statement "Reports in Access are only dynamic if they are based off a query" seems wrong to me. You can run a report with any subset of data by passing a WHERE condition in the DoCmd.OpenReport command. This is one reason to have no criteria in your report's Recordsource, so you can print all the records or any subset as needed. — David-W-Fenton
– David-W-Fenton, Commented May 12, 2011 at 1:07

Thomas · Accepted Answer · 2011-05-11 19:36:20Z

1

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)

answered May 11, 2011 at 19:36

Thomas

64.8k12 gold badges98 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user748872 Over a year ago

Thanks for the input. I've decided that I might be going about the problem the wrong way. I won't be doing any of the filtering, but rather making it easier for the users to build a report off of their filtered data. Right now, without any code to make it easier, the user has to save the filtered table as a query, then open the desired report which is based off the query they just saved(every time they save the filtered table they replace the query that the reports are based off of). How would I write a macro to save the filtered portion of a table as a query?

Thomas Over a year ago

@user748872 - AFAIK, there is no direct means to do that. You need to build the SQL and create the query in code. Keep in mind that you can have a subform which displays your data in datasheet view and do something similar to what I've provided.

user748872 Over a year ago

Thanks for the help! I think in this case that it'll just be one of those things that the user will have to do. It isn't as user friendly as I would have liked, but at least it gets the job done.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related