What is the best data format to export and import data in between applications

[neelsfer]

I have remote datacapturers that capture medical data using an Access application.
This data will then be exported by them on a weekly basis, emailed to me, and then i will import it into a central Access database, to create reports from it.

With Access, they can only export 65000 rows of data.
The application will use a query to export the data from on their side, and on my side it gets imported into a table.

Any suggestion on which data format is the best to migrate large number of rows data, between Access applications? It must also not get to big in size.
Should i use CSV or XML File format?
What export method would be the best to use?
I currently use the following code

Dim cDlg As New CommonDialogAPI         'Instantiate CommonDialog
Dim lngFormHwnd As Long
Dim lngAppInstance As Long
Dim strInitDir As String
Dim strFileFilter As String
Dim lngResult As Long
Dim strBaseName As String
lngFormHwnd = Me.hwnd                           'Form Handle
lngAppInstance = Application.hWndAccessApp      'Application Handle
strInitDir = CurrentProject.Path                'Initial Directory - [UD]
 
'Create any Filters here - [UD]
strFileFilter = "Excel Files (*.xls)" & Chr(0) & "*.xls"
 
lngResult = cDlg.SaveFileDialog(lngFormHwnd, _
            lngAppInstance, strInitDir, strFileFilter)
 
If cDlg.GetStatus = True Then
 strBaseName = Split(cDlg.GetName, ".")(0)
  DoCmd.OutputTo acOutputQuery, "ExportCaptDataIG", acFormatXLS, cDlg.GetName
  MsgBox "All Data is now exported to the folder of your choice. This file can now be emailed or uploaded to the DOH", vbInformation, "Data imported"
Else
  Exit Sub
End If

Imported code

 Dim strPath As String
        With Me
         strPath = Left(.lblFile.Caption, InStrRev(.lblFile.Caption, "\"))
        strPath = FSBrowse(strStart:=strPath, _
                           lngType:=msoFileDialogFilePicker, _
                           strPattern:="MS Excel,*.xls")
If strPath > "" Then
             .lblFile.Caption = strPath
           MsgBox "Please be patient. All Data is now imported into the program", vbInformation, "Data imported"
             Call DoCmd.SetWarnings(False)
Call DoCmd.TransferSpreadsheet(acImport, acSpreadsheetTypeExcel9, "CaptDataImported", strPath, True, "")
  DoCmd.OpenQuery "DeleteCapData"
 DoCmd.OpenQuery "UpdateImportCaptInv"
 DoCmd.OpenQuery "UpdateImportCaptIG"
 Call DoCmd.SetWarnings(True)
 
End If
     End With

[Rabbit]

Why 65000? That limitation is only if you're exporting to Excel.

A delimited file will be smaller than an XML file.

[NeoPa]

There are various Export / Import methods available, but I tend to prefer using emailed BE databases myself. That way I can use PW protected tables and no-one can fiddle with the data outside of the Access databases I provide.

As you probably know by now anyway, the 64K limit is only for Excel 2003 and earlier versions.

[zmbd]

Neelsfer,
Please see your other thread concerning the transfer limit.

I agree with NeoPa about using the backend method to transfer the information... especially anything that is of a private/personal nature which certainly medical records would be classified. You'll not only want a password protected access to the data, you'll also want to encrypt the file.

[neelsfer]

ZMBD - my intention with this thread was to establish a better way/format to export the data with, as i was told in the previous thread that the limit to Excel is 65k.

[TheSmileyCoder]

I think csv is a valid alternative. If the information is confidential, then a encryption of the data would make sense.
It certainly strikes me as relatively easy.

It perfectly possible to create a database file on the fly, and I can see that it might have some benefits over a csv file, such as the ability to include more details on the field types, then I believe is possible with a csv file.

The encryption of data is really a secondary concern to your question. If you have confidential records then encryption should occur regardless of your method chosen.