Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exporting Tables (2000)

    Does anyone know the VB code to export a table from one dB to another to include an argument for password? I want the export table to replace the same named table in the target dB. The files will have to travel over a LAN to another server. As an aside I had/have the problem with code hanging up in VB where the code runs fine on other machines but balks on mine. I have never been able to resolve that issue. I am planning to replace the machine because the time spent on finding the problem became too costly.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Exporting Tables (2000)

    I assume you're talking about Access tables rather than some other kind. Is the password you mention a database password or an Access security password?
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting Tables (2000)

    Yes you are correct an access table. The password is the target database password. I am doing it manually at present using the File|Export command from the drop down menu. When I select the Access db to export it to it asks at that time for the password. I type in the dB password and I am supposing a connection is made. The next step is I get a message box informing me that the table already exists and do I want to replace it. And of course I answer yes. I know it is dangerous but I assume that there are at least three arguments that need to be addressed. (1) Name and path of the target dB (2) password of the target dB (3) a yes answer to replace the existing table. Does all of that make any type of sense?

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Exporting Tables (2000)

    If using database password, not Access security, you can use DAO OpenDatabase method to open password-protected db in code, then use CopyObject command to copy table from current db to target db. Example:

    Public Sub OpenPasswordDB(ByRef strPath As String, ByRef strPwd As String, _
    ByRef strTbl As String)
    On Error GoTo Err_Handler

    Dim ws As DAO.Workspace
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strMsg As String
    Dim strSQL As String

    Set ws = DBEngine.Workspaces(0)
    Set db = ws.OpenDatabase(strPath, False, False, "MS Access;pwd=" & strPwd)

    ' Test connection:
    MsgBox db.TableDefs.Count & " tables.", vbInformation, "TABLE COUNT"
    strSQL = "SELECT * FROM " & strTbl & ";"
    Set rst = db.OpenRecordset(strSQL)
    rst.MoveLast
    rst.MoveFirst
    MsgBox rst.RecordCount & " records in " & strTbl & " table.", vbInformation, "RECORDCOUNT"
    rst.Close

    db.TableDefs.Delete strTbl
    MsgBox db.TableDefs.Count & " tables.", vbInformation, "TABLE COUNT"
    DoCmd.CopyObject strPath, strTbl, acTable, strTbl
    Set rst = db.OpenRecordset(strSQL)
    rst.MoveLast
    rst.MoveFirst
    MsgBox rst.RecordCount & " records in " & strTbl & " table.", vbInformation, "RECORDCOUNT"
    rst.Close
    db.Close

    Exit_Sub:
    Set ws = Nothing
    Set db = Nothing
    Set rst = Nothing
    Exit Sub
    Err_Handler:
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "ERROR MESSAGE"
    Resume Exit_Sub
    End Sub

    Example of use:

    OpenPasswordDB "C:ACCESSPASSWORD.MDB","mypassword","Table1"

    This instruction successfully opened the target db, deleted specified table & replaced with table of same name in current db, without any prompt for password. Test messages are used to confirm that action took place, comment out or delete in actual use. If the table is involved in relationships, further action may be required to ensure relationships deleted, then re-created in the target db. (This can be done with DAO methods). In A2K ensure reference is set to DAO 3.6 type library. For more info refer to VBA Help for OpenDatabase, CopyObject, etc. In actual use, be sure to add more specific error handling in event of invalid path, table name, etc.

    HTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •