access 2010 bloat with forms based on pass through queries

Question

My access 2010 database has super massive bloat. It has gone from 44mb to 282mb in just a few runs of the VBA. Here is what I have set up:

Local tables - these calculate statistics on forms and generally don't move around too much.

Pass through queries - my personal suspect. While viewing a form, if the user clicks on a record I run a pass through query using the record the user clicked on. So user clicked on "joe", pass through query runs with sql string = "select * from sqlserver where name= " &[forms]![myform]![firstname]

After this query runs, my vba DELETES the pass through query, then recreates it after another record is selected. so the user goes back to the list of names, and clicks BRIAN. then my vba deletes the pass through query and creates another one to select all records named brian from sql server, using the same code as above.

Forms - my forms are using the pass through queries as sources.

Is what I'm doing not very smart? How can I build this better to prevent access from exploding in file size? I tried compact and repair, as well as analyze DB performance in access 2010. Any help at all is appreciated, I've been googling access2010 bloat and have read about temptables as well as closing DAO (which I am using, and which I did explicitly close). Thanks!

Here is some code for 1 of the forms i'm using -

Private Sub name_Click()
'set dims
Dim db As DAO.Database
Dim qdExtData As QueryDef
Dim strSQL As String
Dim qdf As DAO.QueryDef
'close form so it will refresh
DoCmd.Close acForm, "myform", acSaveNo
'delete old query so a new one can be created with the same name

For Each qdf In CurrentDb.QueryDefs
If qdf.Name = "QRY_PASS_THROUGH" Then
    DoCmd.Close acQuery, "QRY_PASS_THROUGH", acSaveNo
    DoCmd.SetWarnings False
    DoCmd.DeleteObject acQuery, "QRY_PASS_THROUGH"
    DoCmd.SetWarnings True
Exit For
End If
Next

Set db = CurrentDb
'sql for the data
strSQL = "select fields from (table1 inner join table2 on stuff=stuff and stuff=stuff)     left join table3 on stuff=stuff and stuff=stuff where flag='P' and table.firstname = " &     [Forms]![myform]![firstname]
Set qdExtData = db.CreateQueryDef("QRY_PASS_THROUGH")
'how you connect to odbc
qdExtData.Connect = "ODBC;DSN=server;UID=username;PWD=hunter2;"
qdExtData.SQL = strSQL

DoCmd.OpenForm ("names")
Forms!returns!Auto_Header0.Caption = "Names for " & Me.name & " in year " & Me.year
qdExtData.Close
db.Close
qdf.Close

Set db = Nothing
Set qdf = Nothing


End Sub

And Compact & Repair has no measurable effect? Why did you need to delete the query instead of just feeding it different parameters? Is the WHERE clause the only thing that changes or is it a whole new query? — Brad
Thanks for the reply Brad, I really appreciate it since I hit a wall. The compact and repair took the total file size from 282mb to 281mb. I'm trying to get it back down to a more reasonable 44mb. I needed to delete the query because it would not allow me to create a query with the same name, although I am only changing a single variable in the query in the where clause. So the whole query is identical, and the code for the query is identical, but when a user clicks on the field "name" i want to put that name in the SQL. that's why I have the code above for [forms]![myform]![firstname] — joeumlo

Albert D. Kallal Albert D. Kallal · Accepted Answer · 2014-06-11T17:50:15

There no reason I can think of to not bind the form to a view and use the “where clause” of the open form command. It would eliminate all that code.

You could then simply use:

strWhere = "table.FirstName = '" & me.FirstName & "'"
Docmd.OpenForm "Names”,,,strWhere

Also, it makes little or no sense that a C + R does not return free space. Something else here is seriously wrong.

Also, you really don’t need to delete the query each time. Just assume that the pass-through ALWAYS exists and then use this:

strSQl = “your sql goes here as you have now”
Currentdb.Querydef("MyPass").SQL = strSQL

Docmd.Openform “your form”

The above is all you need. I count about 3 lines of code here that will replace all what you have now. Note that of course the connection string info is saved with the pass-though and does not need to be set or messed with each time.

I would also do a de-compile of your database. I have a short cut setup on all my dev machines so I can just right click to de-compile. Here is some info on de-compile:

http://www.granite.ab.ca/access/decompile.htm

So really, I don’t know why you not using the where clause of the open form? Just bind the form to a SQL view without any conditions. Then use the open form command – you only pull records down the network pipe that match your criteria.

And note how you can stuff your SQL directly into the .SQL property of the query def as above shows – again no need for that delete code and this also means you don’t mess with connection info in code either. Again about 3 lines in total.

access 2010 bloat with forms based on pass through queries

1 Answers