MSysObjects – Queries lists that start with ~sq_

[Programmer - wannaB]

I have been searching the internet and Microsoft almost all day for an answer
to this and I am either not searching right or it’s just not out there. I
would greatly appreciate any help.

I have an access 97 DB that I have imported all objects into an Access 2003
file. After that I reviewed the list of objects by running a query on the
MSysObjects file and found about 143 entries (I call them entries because
they are listed in a query, and I’m not really sure that they are Objects) in
the Name column that all start with ~sq_. Some of the names seem almost
reasonable, like “~sq_ffCountry Profile†OR “~sq_drMain Report~sq_dChild82â€
Are these objects necessary?
How are these created and used?
Once I have cleaned up the database and removed forms queries, and tables
that have been created on the fly and serve no real function, this was a work
in progress and there is a lot that was started and never finished. Should I
Import the necessary Objects into a new MDB to ensure a clean start?
Last Question; Is there a better site then this for a KEY to the TYPE field >>
http://www.perfectparadigm.com/tip001.html

Thank you.

 

[Douglas J. Steele]

Anytime you use SQL (rather than a named query or a table) as the
Recordsource for a form, or as the RowSource for a List Box or Combo Box,
Access creates a query such as you're seeing. I'd guess that ~sq_ffCountry
Profile represents the RecordSource for a form named ffCountry Profile, and
that ~sq_drMain Report~sq_dChild8 is the RowSource for a control named
dChild8 on a form named drMain Report.

Importing only those objects you need into a new database is a reasonable
approach to cleaning up an application. Remember to set any additional
References that may be required.

 

[pietlinden]

I have been searching the internet and Microsoft almost all day for an answer
to this and I am either not searching right or it's just not out there. I
would greatly appreciate any help.

I have an access 97 DB that I have imported all objects into an Access 2003
file. After that I reviewed the list of objects by running a query on the
MSysObjects file and found about 143 entries (I call them entries because
they are listed in a query, and I'm not really sure that they are Objects) in
the Name column that all start with ~sq_. Some of the names seem almost
reasonable, like "~sq_ffCountry Profile" OR "~sq_drMain Report~sq_dChild82"
Are these objects necessary?
How are these created and used?
Once I have cleaned up the database and removed forms queries, and tables
that have been created on the fly and serve no real function, this was a work
in progress and there is a lot that was started and never finished. Should I
Import the necessary Objects into a new MDB to ensure a clean start?
Last Question; Is there a better site then this for a KEY to the TYPE field >>http://www.perfectparadigm.com/tip001.html

Thank you.

Q1: YES! You need them. They're the rowsources for controls in your
forms. Just hide the system objects and you won't have to pay any
attention to them.... but don't delete them... they're there for a
reason. The objects are created by Access behind the scenes to make
your forms etc work. They're necessary... you won't need access to
them unless you want to list objects in your forms..

 

[Ken Snell \(MVP\)]

and that ~sq_drMain Report~sq_dChild8 is the RowSource for a control named
dChild8 on a form named drMain Report.

< g > Probably the RecordSource for a subform named dChild8....

 

[George Nicholson]

If deleted, Access will recreate them. They are temp objects that got
leftover from an improper shut down.

Actually better off to build your db so they never appear. They are deleted
& recreated every time you use sql (rather than a stored query) as a
control/form recordsource. Therefore, they contrbute to database bloat in a
way that stored queries do not.

HTH,

 

[Programmer - wannaB]

Now I'm CONFUSED!!!! I really appreciate all your help, and I am sorry if I
seem stupid.

When I first read Doug’s post I was thinking WOW!! THIS IS GREAT!!! I
UNDERSTAND THIS and it make sense, even to me… Then I read the other 3 posts
and by the time I get to George’s, I am thinking this might make sense too,
and I don’t like bloat…. If the SQL is stored in the form, does it also need
to be here? ALSO how did they get here were they imported along with the
forms??? If it is an OK thing to delete them how would I delete anything
from the MSysObjects table?

ALSO I have this Module that Identifies some of the TYPEs in MSysObjects,
but does anyone know what the others are??? Here are some I am looking to
define
-32757
2
3

And here’s the Function, I modified after retrieving it from the afore
mentioned website.
Function GetObjType(ObjType As Integer) As String
Select Case ObjType
Case -32768
GetObjType = "Form"
Case -32766
GetObjType = "Macro"
Case -32764
GetObjType = "Report"
Case -32761
GetObjType = "Module"
Case 1
GetObjType = "Table"
Case 5
GetObjType = "Query"
Case 6
GetObjType = "Attached Table"
Case Else
GetObjType = " UNKNOWN OBJECT TYPE"
End Select
End Function
=====================================

 

[Pieter Wijnen]

There's really no point in deleting them, the space won't be freed 'till you
run a Compact on the Db. Actually I occasionally do anyway, but only when I
export objects from one Db to another

Here's how:

Dim Db As DAO.Database
Dim Qdef As DAO.QueryDef
Dim i As Integer

Set Db = Access.CurrentDb
For i = Db.QueryDefs.Count -1 To 0 Step -1
Set Qdef = Db.QueryDefs(i)
If VBA.Instr(Qdef.Name, "~") = 1 Then
Access.DoCmd.DeleteObject Access.AcObjectType.acQuery, Qdef.Name
End If
Set Qdef = Nothing
Next
Set Db = Nothing

Pieter

 

[Programmer - wannaB]

Just want to Thank each and every one of you, for your contributions, and
time!!!
Too often I do wonder or worry about the little things...
=======================================

 

[Douglas J. Steele]

A compact will remove some of those, as well, as a compact sets all
SQL to be recompiled. Pre-A2K, it removed all of them, but from A2K
on, it seems to remove only part of them, and I'm not sure why.

I don't know that I'd worry too much about them, certainly not
enough to bother to import into a new MDB!

I assumed that there were other reasons why he was trying to clean up.

I agree there's no point in worrying about those ~sq entries.