Access 2003 on Win XP
I have a test MDB file where I do all my work. When my changes are done, I want to copy the changed objects (forms, queries, reports, modules, etc) to the "work" MDB file.
My current code from Access95/97 is not working. It seems to want an Access username and password. But I would like a way to transfer these objects without setting up Access security. We have problems/bugs with A2003 and MDB files on a network drive, and I want to avoid all those headaches.
How can I transfer an Access object from my MDB on a C: drive to an MDB on my network drive (mapped as a drive letter)?
Below is a picture of my current screen.
I click the Forms, Queries, or Reports button to get a list of that object. The list appears in the left hand list box. When I double click the item in the left list box, it gets transferred to the new MDB file and put in the right list box.
The 2 text boxes above show the current MDB file and the destination MDB file. Filenames erased for privacy.
My problem is, in my current routine (I got it from the internet), it uses an ObjectExists routine, which opens the destination MDB and requires an Access username and password. Here is the first part of the code in ObjectExists:
Wait, the variable of type Database and Workspaces is not a DAO type and I only use DAO. I'll try that. (That didn't work. I still got an error "3029 Not a valid account name or password".)
I have a test MDB file where I do all my work. When my changes are done, I want to copy the changed objects (forms, queries, reports, modules, etc) to the "work" MDB file.
My current code from Access95/97 is not working. It seems to want an Access username and password. But I would like a way to transfer these objects without setting up Access security. We have problems/bugs with A2003 and MDB files on a network drive, and I want to avoid all those headaches.
How can I transfer an Access object from my MDB on a C: drive to an MDB on my network drive (mapped as a drive letter)?
Below is a picture of my current screen.
I click the Forms, Queries, or Reports button to get a list of that object. The list appears in the left hand list box. When I double click the item in the left list box, it gets transferred to the new MDB file and put in the right list box.
The 2 text boxes above show the current MDB file and the destination MDB file. Filenames erased for privacy.
My problem is, in my current routine (I got it from the internet), it uses an ObjectExists routine, which opens the destination MDB and requires an Access username and password. Here is the first part of the code in ObjectExists:
Code:
Function ObjectExists(ObjectType$, ObjectName$, Optional MyDBName As String) As Integer
' Returns TRUE (-1) if an object exists in the current DB. Otherwise
' returns FALSE (0).
' Object type: can be Tables, Queries, Forms, Modules, Reports, Macros
' Object name: Name of object to look for.
' DBName: name of MDB file to check.
'On Error Resume Next
On Error GoTo MyError
Dim Found_Object%, Find_Object As String, ObjectNum As Integer
Dim db As DAO.Database, t As TableDef, ws As DAO.Workspace
Dim Q As QueryDef, c As Container
Dim Msg As String
Dim procname As String
Found_Object% = -1
procname = "ObjectExists"
If SomethingInS(MyDBName) = True Then
' param2=network user, p3=password
Set ws = CreateWorkspace("JetWorkspace", "", "", dbUseJet) ' <=== Error here
Set db = ws.OpenDatabase(MyDBName) ' Destination MDB file.
Else
Set db = CurrentDb() ' Destination MDB file is same as source?
End If
Select Case ObjectType$
Case "Tables"
Find_Object = db.TableDefs(ObjectName$).Name
Case "Queries"
Find_Object = db.QueryDefs(ObjectName$).Name
Case Else
If ObjectType$ = "Forms" Then
ObjectNum = 1 ' ObjectNum used below in db.containers(ObjectNum)
ElseIf ObjectType$ = "Modules" Then
ObjectNum = 2
ElseIf ObjectType$ = "Reports" Then
ObjectNum = 4
ElseIf ObjectType$ = "Macros" Then
ObjectNum = 5
Else
Msg = procname & ": Object Name """ & ObjectType & """ is an invalid"
Msg = Msg & " argument to function ObjectExists!"
MsgBox Msg, 16, "ObjectExists"
Exit Function
End If
Set c = db.Containers(ObjectNum)
Find_Object = c.Documents(ObjectName$).Name
End Select
If Err = 3265 Or Find_Object = "" Then
Found_Object% = 0 ' false
End If
ObjectExists = Found_Object%
db.Close
Set db = Nothing
Set t = Nothing
Set Q = Nothing
Set c = Nothing
If SomethingInS(MyDBName) = True Then
ws.Close
Set ws = Nothing
End If
Exit Function
MyError:
' 3029=Not a valid account name or password.
If Err.Number = 3029 Then
Resume Next
Else
Msg = procname & ": " & Err.Number & " " & Err.Description
MsgBox Msg
ObjectExists = -1
Exit Function
End If
End FunctionWait, the variable of type Database and Workspaces is not a DAO type and I only use DAO. I'll try that. (That didn't work. I still got an error "3029 Not a valid account name or password".)
Last edited:
