Access Capacity

Z

Zuzana

I am trying to create an Access database with linked tables. I would like to
link around 300-500 hundret excell tables into Access. Am I asking for a
trouble? Can you please provide me with information/suggestions you can have
on how to go about it?
Thanks a lot!
 
M

Micah Chaney

I would say you're asking for trouble...but a little more information would
be helpful here. Ask yourself a couple questions.

How are all these spreadsheets related?
Can they be combined into few tables (1 or 2)?
What do you want the database to accomplish?

Remember, the purpose of Access is to take relational data, and create
summarizations based on certain criteria. You can also just store data, but
to build a successful database, you need to have a plan. If you have a plan
and a goal in mind, you can do anything with Access. Except control the
world : )
 
D

Dirk Goldgar

Zuzana said:
I am trying to create an Access database with linked tables. I would
like to link around 300-500 hundret excell tables into Access. Am I
asking for a trouble? Can you please provide me with
information/suggestions you can have on how to go about it?
Thanks a lot!
Click to expand...

If you are *linking* the Excel spreadsheets as tables, you shouldn't
have any capacity problems, because no matter how much data is stored in
those tables, that data is external to the database file itself. Of
course, you probably want to automate the linking process, rather than
going through the whole manual process each time. Given a table of the
spreadsheet files you want to import, you could use the
DoCmd.TransferSpreadsheet method looping through a recordset to link
each one in turn.

If these Excel spreadsheets all contain the same sort of data, you're
probably going to want to append the data from all the linked tables
into one Access table so you can work with it more readily, maybe adding
an extra field in the process to identify which spreadsheet it came
from. Once you've gotten all the data from the spreadsheets into a
local table, you can drop the links. I should say, though, that you can
only do this if the total database file size after the import is less
than the Access maximum of 2GB (1GB if you're using Access 97).
 
A

andrew

In Access Help search for "specifications." It shows that you can have 32,768
objects in a database. My guess is that these could be links. I am linked to
11.9GB worth of .txt files (each a unique table) from one .mdb file. Best of
luck. - Andrew
 
Z

Zuzana

Micah:

Thanks for your tip. Well, the spreadsheet are related but can't be
combined. That is the trick. They get updated individually and have to be
linked individually to reflect the lowest level of detail. Linking them into
Access is a way to be able to query the data easier. Through xls I have
experienced a lot of problems (e.g corupted links). So, basically I want to
link around 200 xls spreadsheet through Access and then query this
information.

Can I experience:
-not reflecting information via the linked table (the table won't update
even though the source xls spreadsheet won't change)?
-slow response time due to so many links?

Thanks a lot!
 
Z

Zuzana

DoCmd.TransferSpreadsheet method looping through a recordset to link
each one in turn.-----

How can I do that?

Thanks!

Zuzana
 
D

Dirk Goldgar

Zuzana said:
DoCmd.TransferSpreadsheet method looping through a recordset to link
each one in turn.-----

How can I do that?
Click to expand...

Suppose you had a table "ExcelFiles" with text fields "FilePath"
(holding the full path to a file to be linked), "SheetName" (naming the
worksheet to be linked within that file), and "TableName" holding the
name of the table to link that file as. Then you could do something
like the following code:

'----- warning: "air code" -----
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("ExcelFiles")

With rs

Do Until .EOF

' Drop table to be relinked, if it exists already
On Error Resume Next
DoCmd.DeleteObject acTable, !TableName
On Error GoTo 0 ' or your error-handling routine

DoCmd.TransferSpreadsheet _
acLink, _
acSpreadsheetTypeExcel9, _
!TableName, !FileName, _
True, _
!SheetName & "$"

.MoveNext

Loop

.Close

End With

Set rs = Nothing
Set db = Nothing
'----- end code -----

Note that the "True" keyword above is saying that the spreadsheet
contains a header row that should be used for field names in the linked
table. If that's not the case, it must be changed to False.
 
Top