Importing from DB that uses a different workgroup 3

[DeanWilliams]

Hi,

I want to import objects from one secure dbase to another, but they are using different workgroup files. I get insufficient permissions messages. What is the easiest way to do this?

Thanks,

Dean.

 

[psemianonymous]

Use an intermediary unsecured MDB. Open up Access using #1.MDW, then create a new database. Go to File->Import and import all necessary items from the source MDB.


Close Access.


Open access back up with the #2.MDW in the destination file. Do another File->Import, and pull all items from your "intermediate" MDB file.


That will work.

 

[psemianonymous]

ALTERNATE: open both databases in separate Access windows. Use the clipboard (i.e. copy/paste) to copy the objects, one at a time.

 

[DeanWilliams]

Hello,

Thanks for the suggestions, but neither of them work. I still get permission errors. I am wondering if I could play with the Admin account.

Any other suggestions?

Dean.

 

[psemianonymous]

No, I promise that should work. Hmm, maybe...

do the intermediary step. Move from the secured to an unsecured database.

ADDED STEP: now go into the permissions for your object(s), and explicitly assign full permissions for the Users group for all the objects.

Then go and import the objects into database #2.


It should work.

 

[oharab]

Instead of that, I would use ADO and connect to the external database.
ADO connection strings allow you to specify a mdw and user credentials for the connection. Check out the example below.

hth

Ben

Sub testLink()
Dim connExternal As New ADODB.Connection
Dim rstExternal As New ADODB.Recordset
Dim connInternal As New ADODB.Connection
Dim rstInternal As New ADODB.Recordset
Dim fldExternal As ADODB.Field

connExternal.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\PathTo\Database.mdb;SystemDB=C:\PathTo\MDWFile.mdw;", "UserName", "Password"
'Connect to external database
Set connInternal = CurrentProject.Connection
'Connect to current database



rstExternal.Open "SELECT * FROM tblAutoNumber", connExternal, adOpenDynamic, adLockReadOnly
'open table to import from
rstInternal.Open "SELECT * FROM tblAutoNumber", connInternal, adOpenDynamic, adLockOptimistic
'open table to import to

Do Until rstExternal.EOF
'until we've copied every record
rstInternal.AddNew
For Each fldExternal In rstInternal.Fields
rstInternal(fldExternal) = rstExternal(fldExternal)
'copy the contents of the external database into the new
Next fldExternal
rstInternal.Update
'save the record
rstExternal.MoveNext
'get the next one
Loop

rstExternal.Close
connExternal.Close
rstInternal.Close
connInternal.Close
'close and tidy up
Set rstExternal = Nothing
Set connExternal = Nothing
Set rstInternal = Nothing
Set connInternal = Nothing

End Sub


----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------

 

[DeanWilliams]

Hi,

Thanks for the help Ben. I don't use ADO as much as DAO, but I wanted to import all the objects, as opposed to data (Tables, Queries, Forms, Reports and Modules as well as custom toolbars, menus etc).

I don't have time at the moment to read up on ADO to find out if this is possible, so any more help would be appreciated.

Cheers.

Dean.

PS: Happy New Year everyone!!

 

[DeanWilliams]

Thanks guys,

Foolio: Thanks very much. The step of adding permissions to the Users group worked. I thought it was something like that. Have a star.

Dean

 

[HitechUser]

oharab,

oharab (if you can) or anyone else. I am trying to get the ADO example to work. The connections are fine. My problem is the when I execute the following statement I get and error.

rstInternal(fldExternal) = rstExternal(fldExternal)

Run-time error '3265'
Item cannot be found in the collection corresponding to the request name or ordinal

I am running Access2K. I need this example to work since I need to connect using different .MDW's.

I hope someone can help.

Thanks in advance.

 

[PHV]

The 2 tables must have identical structure.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[HitechUser]

PHV,

Thanks for your quick reply. The tables both have three columns.

As a test, I copied the MDB and changed the code (in the original MDB) to point copy MDB. Then I ran the code from the original MDB and yet I still get the same error.

I am "sure" the tables are the same. Any suggestions?

Thanks again in advance.

 

[PHV]

Have you tried this ?
rstInternal(fldExternal.Name) = rstExternal(fldExternal.Name)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[HitechUser]

PHV,

You rock!!

That solved it.

Thanks again