Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2008
    Posts
    20

    Unanswered: Table Import Export

    I wish to make a command button for exporting selected tables. Is it possible the tables get encrypted? Also I want another command button to import the exported tables… This is because I want to work by transferring data from different countries using the same database gui and exporting tables from my ‘server database’ and import them on other ‘slave’ computers and vice versa. Thanks guys

  2. #2
    Join Date
    Aug 2006
    Posts
    559
    Instead of importing and exporting, since you'll have multiple versions of the tables on different machines (one for each user that imports it), why don't you just link the tables to the front-ends of the dbs and allow all users to have access or deny access based upon user level / password protection on forms or command buttons?

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ▲ Because the machines are in different countries!

    I wish to make a command button
    Don't wish for it, just do it!

    Sounds to me like you need to host the data on the Internet in something like a MySQL Server.

    However, you can set up Import and Export specifications and call them using VBA from a command button.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    This might help to show you an "Importing" routine which you could then utilize as an exporting routine also with a few modications.

    http://www.dbforums.com/showpost.php...6&postcount=24

    As far as exporting it encrypted, that might be a parameter in the docmd.transfertext command or established in the export specification file which you would use as a parameter in the docmd.transfertext command.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Sep 2008
    Posts
    20
    Hi pkstormy yap, yap... this is exactly what i mean!!! but is it hard to perform me the export command also ?

    To add, i might bite the hand that feeds me but can the export/import buttons perform exports of specified tables? I mean, immagine there are 7 tables and i need to export tables 1 4 and 5. The command will export only the 3 tables. Vice versa, the import button will import back tables 1 4 and 5 back to their places. Any ideas guys?

    Again, Thanks a lot for your time mates cheers
    Last edited by 8thstudios; 09-05-08 at 16:37.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I don't understand the question... if you make an export for table 3 then that is what it will export.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Sep 2008
    Posts
    20
    Quote Originally Posted by StarTrekker
    I don't understand the question... if you make an export for table 3 then that is what it will export.
    I mean, making a command button that automatically exports tables without browsing for them....

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You can make a command button that exports data that makes the user select a table??

    HA! Didn't know that one

    Maybe if you could detail what you currently have and CLEARLY indicate what you want, it might help us to help you. I still don't know what you want.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Sep 2008
    Posts
    20

    Thumbs up

    Quote Originally Posted by StarTrekker
    You can make a command button that exports data that makes the user select a table??

    HA! Didn't know that one

    Maybe if you could detail what you currently have and CLEARLY indicate what you want, it might help us to help you. I still don't know what you want.
    Its ok star, i also did this getting deeper in the codes... but special thanks goes to Pkstormy. Thanks mate

  10. #10
    Join Date
    Sep 2008
    Posts
    20
    Just to simply the things out of here. This is the command i have that performs the export of tables. This is how it works at the moment: when i select the fields exportcustomers and exportvoucher, the cmdexportdata will export the data to a location i decide.

    I tried to modify the code that when i press the cmdexportbutton, the command will export without i have to select the fields exportcustomers and exportvoucher. but i failed!

    i also wish to remove the xls and and dbase format that are in here. any help?

    Private Sub cmdExportData_Click()
    If IsNull(Me!exportcustomers) Or Me!exportcustomers = "" Then
    MsgBox "You must select a Query to Export first!"
    Me.exportcustomers.SetFocus
    Exit Sub
    End If

    If IsNull(Me!exportvoucher) Or Me!exportvoucher = "" Then
    MsgBox "You must select a Query to Export first!"
    Me.exportvoucher.SetFocus
    Exit Sub
    Else
    'Check for null start and end dates
    If IsNull(Forms!datatran!startdate) Or IsNull(Forms!datatran!enddate) Then
    MsgBox ("You must first enter the Start Date and End Date above!")
    Exit Sub
    End If
    'Everything ok - go with export
    Refresh
    'Make temp totals table if it is not already made. Note: I do this for extremely large recordsets so it doesn't take minutes to re-run them.
    'Run this routine to export data which is based on date criteria as you need to make the table using date criteria.
    'If Forms!ReportForm!TotalsMade = False Then
    'Make a new totals table if need be.
    ' Call MakeTotalsDateEntered
    'End If

    Me!lblFeedback = "Standy by, transfering data......"
    Me.Repaint

    On Error GoTo NoExport
    Select Case Me!ExportType
    Case "csv"
    DoCmd.TransferText acExportDelim, , Me!exportcustomers, Me!ExportFolder & "\" & Me!exportcustomers & ".csv", True
    DoCmd.TransferText acExportDelim, , Me!exportvoucher, Me!ExportFolder & "\" & Me!exportvoucher & ".csv", True


    Case "xls"
    If Me!AddDate = True Then
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, Me!ExportQuery, Me!ExportFolder & "\" & Me!ExportQuery & Format(Date, "mmddyyyy") & ".xls", True
    Else
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, Me!ExportQuery, Me!ExportFolder & "\" & Me!ExportQuery & ".xls", True
    End If
    Case "dbase"
    Dim strDB As Variant
    strDB = CurrentDb.Name
    'C:\HelpForOthers\PK5\SampleReportingSystemNewVers ion.mdb
    'Ex: DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\DBS\NWSales.mdb", acReport, "NW Sales for April", "Corporate Sales for April"
    ' strCurrentDir = Left(strDB, Len(strDB) - Len(Dir(strDB)))
    Dim NExportName As Variant
    TryAgain:
    NExportName = InputBox("Since 8 character names are allowed for exporting dbf files, enter the 8 character name to export as:")
    If IsNull(NExportName) Or NExportName = "" Then GoTo NoExport
    If Len(NExportName) > 8 Then
    MsgBox "You can only enter up to 8 characters."
    GoTo TryAgain
    End If
    DoCmd.TransferDatabase acExport, "DBase III", Me!ExportFolder, acQuery, Me!ExportQuery, NExportName & ".dbf", False
    End Select
    'DoCmd.TransferText acExportDelim, , "MeasureTotals", Me!ExportFolder & "\CustomerData.csv", True
    MsgBox "Data transfered."
    Me!lblFeedback = "Successful, Data Transfered!"
    Me.Repaint
    Done:
    End If
    Exit Sub
    NoExport:
    MsgBox "The data was NOT transfered. Contact Administrator!"

    End Sub

  11. #11
    Join Date
    Sep 2008
    Posts
    20
    Anybody guys please?

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Since this code looks familiar, you may want to change this...
    DoCmd.TransferText acExportDelim, , Me!exportcustomers, Me!ExportFolder & "\" & Me!exportcustomers & ".csv", True
    DoCmd.TransferText acExportDelim, , Me!exportvoucher, Me!ExportFolder & "\" & Me!exportvoucher & ".csv", True

    to just export the specific tables you want to export versus based on what is selected on the form (ie. in the ExportVoucher and ExportQuery fields.)
    ie..
    DoCmd.TransferText acExportDelim, , "MyTableName", Me!ExportFolder & "\MyTableName" & ".csv", True (to export the table named: MyTableName)
    DoCmd.TransferText acExportDelim, , "MyTableName2", Me!ExportFolder & "\MyTableName2" & ".csv", True (to export the table named: MyTableName2)

    which in reality, might translate to...
    DoCmd.TransferText acExportDelim, , "MyTableName", "C:\SomeFolder\MyTableName.csv", True (if for example, the user selected C:\SomeFolder using the Browse button to select for me!ExportFolder)


    The ExportVoucher and ExportQuery fields were designed on the form to allow you to select what "query" you want to specifically export but in your case, it sounds like it might be better to remove these fields and just statically code into the docmd.transferxxxx the actual table (or query) names you want to export.

    You may want to look up the help on the docmd.transfertext/docmd.transferspreadsheet. If you start entering the command: docmd.transfertext acexport,.....
    you begin to see the popup help text on what you should enter next (ie. [TableName],[FileName]) and so on. All the code really does is allow you to select the table/query name to export and the folder location you want to export to and combines these values in the docmd.transferxxxx but other than that, it follows the same parameters sequence used if you search the help on this.

    Again, use the example I posted to help you design the form as you need to. The biggest thing I wanted to show in the example was the ability to use the Browse button and select the folder to do the export to (me!ExportFolder) and how it can be combined to use with the docmd.transfer....versus statically programming in an exact folder location to export to.

    So for example, this....
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, Me!ExportQuery, Me!ExportFolder & "\" & Me!ExportQuery & Format(Date, "mmddyyyy") & ".xls", True

    might be

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MyTableName", Me!ExportFolder & "\MyTableName" & Format(Date, "mmddyyyy") & ".xls", True
    or
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MyTableName", Me!ExportFolder & "\WhateverIwantToNameMyExportFile" & Format(Date, "mmddyyyy") & ".xls", True
    or
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MyQueryName", Me!ExportFolder & "\WhateverIwantToNameMyExportFile" & Format(Date, "mmddyyyy") & ".xls", True
    or (not using any of the text fields on the form)...
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MyQueryName", "C:\SomeFolder\WhateverIwantToNameMyExportFile " & Format(Date, "mmddyyyy") & ".xls", True

    (again - notice how I simply replaced some of the parameters in the docmd.transferxxxx with the values of the unbound text fields on the form (ie. "MyQueryName" versus me!ExportQuery and C:\SomeFolder with me!ExportFolder.)

    Replacing MyTableName or MyQueryName above with the actual table name (or query name) that you want to export.

    Hope that helps. If you still need help, try posting your mdb and we could take a look at it.
    Last edited by pkstormy; 09-15-08 at 22:04.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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