How do I split a big file into smaller files went my form 1

[kat25]

I have a database already designed and works great, however,
I link to a large excel spreadsheet. I am now ready to put the application on my server. I'm afraid with this database running on the server that the response time in accessing the data of this spreadsheet will be very slow.
I thought if I could split the spreadsheet into smaller files that the user's response would improve. I have alot of code and queries built currently to the one large spreadsheet. I am using Access 2000.
Any suggestions will be most appreciated.
kat25

 

[psemianonymous]

If it's terribly slow/unworkable, consider the possibility of running "manual updates" of the spreadsheet data into a "local table" copy of the spreadsheet. That way you'll get a huge performance boost--at the cost of the "freshness" of your data. Your data is only as new as your last update, but it is faster.


Otherwise, see if the speed difference is noticeable before doing anything drastic. --
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

 

[kat25]

foolio12
Can you explain to me how the "manual update" process
works?
Thank you.

 

[psemianonymous]

Basically, here's how I see it. Two different parts:

1. Part one: setting it up:
a. back up your database.
b. Rename your linked Excel spreadsheet "table" to something else. I'm going to go with "xl".
c. Create a new table with the same field names as your linked table "xl". The easiest way to do this is to make a new "Make-table query" using "xl" as the source, and all fields of xl poured into the new table.

2. Part two: an update.
--A small pointer: the easiest way to update your information is to simply delete the table and then re-update it. If your table is "linked" to other tables, you can simply delete the contents of the table, and then re-append them. This isn't an issue unless you have multiple users accessing the database while you run an "update"--it screws up all their currently open queries/forms/etc because several items will say "#Deleted". Moving on:
a. run a delete query which deletes all records.
b. run an append query which appends all items in "xl" to "tblMYTABLENAME".


You can make a macro which will do the updates, or if you have an application, of course it can be done from VBA. --
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

 

[kat25]

foolio12
Thank you for the detail information.
I appreciate your help.

kat25