Download data from Access database into Excel

[J]

Hi,
Is there a way to present users with a functionality that
downloads information from a table in an Access database,
on a pasword protected server, into Excel/Access on the
local machine.

Thanks.

J

 

[Tim Ferguson]

Is there a way to present users with a functionality that
downloads information from a table in an Access database,
on a pasword protected server, into Excel/Access on the
local machine.

To export into an existing Access table, use something like

INSERT INTO MyNewTable (FieldOne, FieldTwo, FieldThree)
IN c:\MyLocalDatabase.mdb
SELECT OneField, TwoField, Three
FROM MyServerTable
WHERE ToBeExported=TRUE;


For details on how to import into an existing Excel file, check the help
files for the IN clause (SQL).

To create a new object (table or workbook) look up the TransferSpreadsheet
etc. commands.

Hope that helps


Tim F

 

[J]

Thanks for your reply Tim.

I tried your first suggestion and it worked fine when I
wrote my SQL stmt directly in Access. However, when I try
to put it in an ASP script and use that from my server,
it throws out this error:

Microsoft JET Database Engine error '80004005'

'C:\TheWholePath\LocalDbase.mdb' is not a valid path.
Make sure that the path name is spelled correctly and
that you are connected to the server on which the file
resides.

The same thing happens when I try to use the
TransferDatabase action in a macro.

I'm totally lost coz literature on the error '80004005'
seems to address everything but file paths.
Could it have anything to do with file permissions and
the fact that I'm trying to download information from an
Access database on a password-protected server to my
local database.

Please help.
J

 

[Tim Ferguson]

I'm totally lost coz literature on the error '80004005'
seems to address everything but file paths.
Could it have anything to do with file permissions and
the fact that I'm trying to download information from an
Access database on a password-protected server to my
local database.

Not sure that I can help here either: sounds like a network mgmt issue.
Remember that it's the ASP server that has to be able to see the file, not
you... have you tried using a UNC path rather than c:\ ?

What happens if you do a

MsgBox Dir("C:\TheWholePath\LocalDbase.mdb")

in the script: this will show whether the script can see the file or not. I
have to admit, though, that I am in the dark with this.

All the best


Tim F