import from and export to "my documents"

[P Nahser]

I have an application that uses export/import to back up data ( i.e. each
day, the user exports data to "my documents" and then has the data available
for import in case of program failure or when I update the application).

Export code
Private Sub Command11_Click()
On Error GoTo Command11_Click_Err

DoCmd.GoToRecord acForm, "gxt version 3", acNext
DoCmd.GoToRecord acForm, "gxt version 3", acPrevious
DoCmd.OutputTo acTable, "GXT Cardiolite table",
"MicrosoftExcelBiff8(*.xls)", "", False, "", 0

Exit Sub
Command11_Click_Exit:
Exit Sub

Command11_Click_Err:

MsgBox "You have not selected anything to save to export",
vbInformation, "RS, Inc."

Resume Command11_Click_Exit

End Sub



Import code

Private Sub Command12_Click()

On Error GoTo Command12_Click_Err

DoCmd.TransferSpreadsheet acImport, 5, "gxt cardiolite table", "gxt
cardiolite table", True, ""

Command12_Click_Exit:

Exit Sub

Command12_Click_Err:

MsgBox "You have not selected anything to import", vbInformation, "RS,
Inc."

Resume Command12_Click_Exit

End Sub


The export code successfully exports the table to the "my documents" folder.
The import code imports a file by the same name but it is an older file that
was exported in the past- it is not the same one I just exported to the "my
documents" folder.
I have been messing around with this application and have multiple versions
and pieces of the application in various stages of development and possible
my application is importing the file from one of the many versions that I
have ??????


Questions:
1. How can I export the data ( allowing the user to specify the file name)
to "my documents" and then import that same data (specifically from "my
documents")
Is it OK to export using "output to actable" and import using
transferspreadsheet?
How do I allow the user to specify the file he wants to import when
using transferspreadsheet?

Thanks

Phil

 

[strive4peace]

Hi Phil,

you should specify the path for the source file instead of just the
filename -- and you need to specify the file extension* (xls if it is an
Excel file) -- Access is obviously looking in the wrong place...

here is the syntax for the TransferSpreadsheet method

DoCmd.TransferSpreadsheet(
TransferType
, SpreadsheetType
, TableName
, FileName
, HasFieldNames
, Range
, UseOA
)

ie: -->

DoCmd.TransferSpreadsheet
acImport
, 5
, "gxt cardiolite table"
, "c:\data\access\gxt_cardiolite.xls"
, True

It is not a good idea to use spaces in filenames or put get files from
directories containing a space in the path... it is also good to keep
the paths short...

I would also rename the table to not have a space...

gxt cardiolite table --> gxt_cardiolite

spaces create problems... so don't use them -- except in the data of
course

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`

Instead of using My Documents (contains a space -- will cause problems
-- and is long), create a directory called

C:\Data

and put all data below that...

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`

*** to show file extensions ***

from the menu of windows Explorer or My computer:

Tools, folder Options...
View tab -->
UNCHECK "Hide file extensions..."

as a general rule:
1. UNCHECK/deselect anything that starts with "Hide" or "Don't show" or
reads something like that...
2. CHECK everything that starts with "Display" or "Show" or something
like that...

....you don't want to hide things from yourself...



Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[P Nahser]

Thanks Crystal,

Do I have to change the name of tables, reports, queries, (in order to
eliminate spaces )in the application or just in the export/import aspects of
the program? Thanks, have a great day.

Phil

 

[strive4peace]

Hi Phil,

is is best not to use spaces anywhere! the immediate concern, however,
is the path and filename(s) that you are using...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*