Hello again -
Actually, I do have one additional suggestion. Since the database documentor
can see your missing queries, you might try adding the following code to a
new module in your new database. Run the code and see if it can import these
hidden queries. Select the Modules tab in your new database. Click on New to
create a new module. Click on Tools > References... Scroll down the list
until you find a reference that reads "Microsoft DAO 3.6 Object Library".
Place a tick to select this reference. Save the module as
"basGetRemoteQueries" (without the quotes). Then copy the following code and
paste it into your new module:
Option Compare Database
Option Explicit
Sub RetrieveRemoteSQL()
On Error GoTo ProcError
Dim acc As Access.Application
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim dbRemote As DAO.Database
Dim qdfRemote As DAO.QueryDef
Dim qdfRemoteColl As DAO.QueryDefs
Set db = CurrentDb()
Set acc = New Access.Application
Set dbRemote = acc.DBEngine.OpenDatabase _
("G:\Databases\RemoteDBName.mdb") '<--Name of source DB
Set qdfRemoteColl = dbRemote.QueryDefs
For Each qdfRemote In qdfRemoteColl
If Left$(qdfRemote.Name, 1) <> "~" Then
Debug.Print qdfRemote.Name: Debug.Print
Debug.Print qdfRemote.SQL: Debug.Print
Set qdf = db.CreateQueryDef(qdfRemote.Name, qdfRemote.SQL)
End If
Next qdfRemote
ExitProc:
On Error Resume Next
qdf.Close
Set qdfRemote = Nothing
Set qdfRemoteColl = Nothing
dbRemote.Close: Set dbRemote = Nothing
Set acc = Nothing: Set qdf = Nothing
db.Close: Set db = Nothing
Exit Sub
ProcError:
Select Case Err.Number
Case 3012 'Query already exists
Resume Next
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure RetrieveRemoteSQL..."
End Select
Resume ExitProc
End Sub
' **************Code End********************
Make sure that you do not end up with duplicate statements at the top of the
new code module:
Option Compare Database
Option Explicit
If you do, then delete any duplicate statements shown above. Substitute the
full path with filename in the above statement that reads:
Set dbRemote = acc.DBEngine.OpenDatabase _
("G:\Databases\RemoteDBName.mdb") '<--Name of source DB
Then click on Debug > Compile ProjectName. Hopefully the code will compile
without any errors.
With your mouse cursor anywhere within this new procedure, press the F5
button. Are you now able to recover your missing queries?
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
:
Hi Steve,
Yes, but only one....
Make sure that any missing objects have not somehow been set as either
hidden or system objects. Click on Tools > Options... | Select the View tab.
Place a tick into both the Hidden Objects and System Objects checkboxes. Can
you see your missing queries now? Note: you should also see several tables
whose names begin with MSYS. Don't mess around with these system tables.
Also, do not attempt to import them into the new database.
If your missing queries all of a sudden show up, but their names have a
lighter appearance in the database window, then the hidden attribute somehow
got set for these queries. Right-click each one, select Properties from the
short cut menu, and see if there is a tick in the Hidden attribute checkbox.
Good Luck.
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
:
Hello Tom:
Thanks for your reply.
The computer is fully updated with Windows updates, Office 2000 service
packs and with Jet updates.
I tried what you suggested: I created a blank DB and tried to import the
missing queries.... they're missing from the import list as well. It's
strange, because when I do Tools/Analyze/Documentor, they all show up in the
query list.
Arghhhh
Any other suggestion?
Steve
__________________________________________
:
Hi Steve,
Have you applied all service packs to the operating systems, Office, and the
JET database engine on both computers? Here is a KB article that shows you
how to do this:
How to keep a Jet 4.0 database in top working condition
http://support.microsoft.com/?id=303528
What happens if you create a brand new Access database on the Windows 98 PC,
and then attempt to import objects (tables, queries, forms, reports, etc.)
from the original database? Do the missing queries show up all of a sudden?
If my answer has helped you, please answer yes to the question that reads
"Did this post answer the question?" at the bottom of the message thread.
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
:
Hello:
I've developed an Access DB using Office 2003 (in 2000 file format), WinXP.
I then copied this DB to a Win98 computer using Office 2000. Only 1/2 of my
queries show up. Yet, when I tried to recreate one (with the same name) in
the DB on the Office 2000 computer, it says an object of that type already
exists. Yet the VB code referencing it fails saying it can't find the object.
Where is it? How can I "reactive"? the queries?
I have set up no security on this DB yet.
Thanks
Steve
