copy table in external protected database

[Jan Lichtenbelt]

I have two access databases, both password protected. Now I want in database1 copy a table in database2, using VBA.
Up to now I found an indirect solution which imports and exports a table. This imports the table from database2 to database1, renames the table and exports the renamed table from database1 to database2.
But there must be a more direct copy procedure, not via database1.

Can someone helps me?

Kind Regards

Jan Lichtenbelt

PS> I'm using Access2003

 

[Guus2005]

Did you try DoCmd.TransferDatabase ...?
Use Access help for a more detailed explanation.

HTH

 

[Jan Lichtenbelt]

Hi Guus,

That is what I did:
1)docmd.Transferdatabase acImport ..... (copy table from db2 -> db1)
2)rename the table (all in db1)
3)docmd.Transferdatabase acExport ..... (copy table from db1 -> db2)
4) delete temporary table (in db1)
Perhaps I have to live that this all cannot be done in db2. Db1 is needed for the temporary table

Kind regrads

Jan Lichtenbelt

 

[Leif]

May I ask how you got it to work with two password protected databases? I would like to transfer (or link) to tables in another database, however, each database as a different mdw file. When I try to use the transfer database I get the message Run-time error 3033, "You do not have the necessary permissions to use the {database name} object. Have your system administrator who created this object establish appropriate permissions for you".

Trouble is, I am the system administrator who created both databases, yet I don't know how to get them to talk.

Thanks,
Leif

 

[Jan Lichtenbelt]

Dear Leif,

If your are logged in the first database, you can copy a table in the second, protected, database using the subroutine below.

Kind regards

Jan Lichtenbelt
-------------------

Sub CopyTableInExternalDatabase(FileNameSecondDatabase, TableNameIn, TableNameOut, Password)
' March 8 2010, Jan Lichtenbelt, the Netherlands
Dim ws As Workspace
Dim dbs As Database
Dim LFileName As String
Dim Conditions as String
Dim TableNameTemp as String

On Error GoTo Wrong

'Make sure access filen LFileName has full path in front
LFileName = FileNameSecondDatabase

'check access file exist
If Dir(LFileName) = "" Then GoTo Wrong

'Get default Workspace
Set ws = DBEngine.Workspaces(0)

'open the access file
Conditions = "MS Access;PWD=" & Password
Set dbs = ws.OpenDatabase(LFileName, False, False, Conditions)

'For data entry tables, import only tables from access file
TableNameTemp = "Temp_Table"
If TableExist(TableNameTemp) Then DoCmd.DeleteObject acTable, TableNameTemp

DoCmd.TransferDatabase acImport, "Microsoft Access", LFileName, acTable, TableNameIn, TableNameTemp
DoCmd.TransferDatabase acExport, "Microsoft Access", LFileName, acTable, TableNameTemp, TableNameOut
DoCmd.DeleteObject acTable, TableNameTemp
dbs.Close
Set dbs = Nothing

Exit Sub
Wrong:
Set dbs = Nothing
MsgBox err.description
End Sub

 

[Jan Lichtenbelt]

1) You need still the TableExist function, see below.
2) And if the both databases are in the same directory, you can replace
LFileName = FileNameSecondDatabase
LFileName = CurrentDbDir & FileNameSecondDatabase
3) if both databases has the same password you can use the function GetPassword.

succes

Jan Lichtenbelt
------------------------

Function TableExist(TableNameToTest) As Boolean
' November 22, 2006, Jan Lichtenbelt, the Netherlands
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
' Search for open AccessObject objects in AllTables collection.
TableExist = False
For Each obj In dbs.AllTables
TableExist = TableExist Or (UCase(obj.Name) = UCase(TableNameToTest))
Next obj
Set dbs = Nothing
End Function

Function CurrentDBDir() As String
' December 1, 2005, Jan Lichtenbelt, the Netherlands
Dim strDBPath As String
Dim strDBFile As String
strDBPath = CurrentDb.Name
strDBFile = Dir(strDBPath)
CurrentDBDir = Left$(strDBPath, Len(strDBPath) - Len(strDBFile))
End Function

Function GetPassword()
'July 2, 2007, Jan Lichtenbelt, the Netherlands
Dim dbs As Database
Dim rst As Recordset
Dim Counter As Integer
Dim I As Integer
Dim Password

On Error GoTo Wrong

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("MsysObjects")
With rst
.MoveLast
.MoveFirst
Counter = .Recordcount
For I = 1 To Counter
If !Connect <> "" Then Password = !Connect
.MoveNext
Next I
.Close
End With
Set dbs = Nothing
Password = Mid(Password, InStr(Password, "PWD=") + 4)
GetPassword = Left(Password, Len(Password) - 1)
Exit Function

Wrong:
MsgBox Err.Description
End Function

 

[Leif]

Hi Jan,

Thanks for your reply. In looking at your code I think we may be discussing two different cases. It appears you are dealing with password protected databases. In my case I have user level (.mdw) protected databases. The databases don't have an overall password, but are access protected through individual user login control.

I was looking at your code and wondering why you are providing a password and not a user ID. Then it hit me. I'll take a closer look at your code and see if it can be adapted. I know I can do a remote login, using recordsets, to the second database. I can then copy individual records over. I was hoping for a way to simplify that a bit, such as using a database link or transferdatabase command. The jury is still out.

Thanks,
Leif

 

[Jan Lichtenbelt]

Hi Leif

Owner protected databases are new fo me. Tell me where I find more about this subject?

Kind regards

Jan

 

[Leif]

Not Owner protected databases, but user level security. You can read up by checking into the Access security wizard.

I just found another source of information on Access security on the web. I've been looking for something that goes into detail such as this. Its not an easy read, but it give you a good understanding of what is going on.

www.grahamwideman.com/gw/tech/access/accesssec/index.htm