Excel to Access

[Guest]

Is it possible to have an access database updated from an Excel
workbook/worksheet. I want the database to be updated everytime the Excel
file is updated. Or, is it possible to create an access database that will
include the web queries that are created in an excel file?

Thanks

 

[Tom Ogilvy]

In access, can't you link to an excel range and create a database from that?

 

[Guest]

That may be possible, however, I run the query every evening and would like
the info to be transferred directly to the access database. Otherwise, is it
possible to use excel as a database in coldfusion? If that is true, it would
be much easier just to use the excel file as a database.

 

[Guest]

From Access you can use DoCmd.TransferSpreadsheet to either link ("live"
updates) or import("snapshot") the Excel data - see the Help file in Access
for details. As far as updating whenever Excel updates, there is no way for
Access to know when the Excel file has been updated without some sort of
two-way communication going on - meaning either a link or some code in the
Excel workbook that triggers an export to Access. But you should be able to
set up an import routine in Access and run it when you start up the Access
app, or when you press a button, or even linked to a timer if desired.

And I am sure that with some creative VBA coding in Access you could import
directly off the web, duplicating a web query, but that is not something I
have any direct experience with...

 

[Guest]

Link to the xl file range.
file>get external data>link tables. follow the wizard.
this link will be just like an access table and can be use
anyway an access table can be used.
I do it all the time. keeps the size of the access db
down, save disk space by eliminating redundunt data.
Why have the same data in two places?

 

[Guest]

But, do I have to do this each and every time I want to update the database?
That would be rediculously tedious. I have some 20-30 worksheets and the
range changes daily.

 

[Guest]

Todd'
you asked about using Excel as a database alone w/out Access.
What is the max # of records and fields would you have in any table?

I have an excel "db" with 65000 records and 39 fields that I can sort,
filter,search,add/delete/edit records.

Does this meet your requirements?

 

[Guest]

Thanks gocush,

I think that will work, I don't have nearly that many records. Do you use
coldfusion, I will be.

Thanks,
Todd

 

[onedaywhen]

Is it possible to have an access database updated from an Excel
workbook/worksheet. I want the database to be updated everytime the Excel
file is updated. Or, is it possible to create an access database that will
include the web queries that are created in an excel file?

It seems to me you want to detect the change in Excel in real time and
notify the database of the changes at this time e.g. open an ADO
connection and issue some SQL (this would discount using the MS Access
app).

What changes are being made? If it was a user editing a worksheet, I'd
suggest using an event (e.g. Worksheet_Change) and issuing an INSERT
INTO (new row) or UPDATE (amended row) command with the appropriate
column values or a DELETE command. To be able to associate a row in
Excel with its corresponding row in the database, I think you will need
to use a key column(s) common to both and use it in the aforementioned
command. You'd also have to lock down some columns/rows to try to avoid
the illogical things users are apt to do (deleting the column
containing the key values or the header row, destroying row continuity
by shifting down cells, etc) but allow then to flag a row as new or for
deletion.

If it is a bulk change (e.g. following the refresh of an embedded
query) you'd again need a key to be able to make a comparison to the
data in the database to detect which rows are to be
inserted/amended/deleted. With Jet, you can create JOINs in SQL between
the data in Excel and the .mdb to see these changes. See a previous
post by searching google groups for the exact phrase, "basic 'template'
for comparing".

....Unless the database is being used as a 'dumb' data store, then you'd
just DROP the table (unless it was a 'history' table with a timestamp
column <g>)and SELECT..INTO to recreate using all Excel rows in one
hit.

Jamie.

--