Linking to external tables (1 Viewer)

[Inspired]

Hi All,

I have a problem linking to tables in an external Access database.

I have to run through a loop potentially 1000’s of times, and each time my code needs to link to an external table (depending on certain criteria), extract the data and manipulate it. Then my loop deletes the link and begins the whole process again. The code that I’m using to create the link is as follows:

appAccess.DoCmd.TransferDatabase acLink, "Microsoft Access", strExternalDB, acTable, strTableName, "tblData"

The problem occurs initially at the beginning of the loop when I get a debug saying “Run-time error 3045. Could not use pathtodatabase\db.mdb; file already in use.” However, all I need to do after this debug is press continue or F5 etc, and the code continues running perfectly for the rest of the loop, without me having made any changes.

I have thought about incorporating some error handling, for example, that if Err.Number = 3045 then attempt to re-link again but this has been to no avail.

If it makes any difference – not that I think it does – it is actually already an external database (appAccess in my code snippet above) that is linking to external tables in yet another database. I.e. My main database (DB1) is running code and manipulating data in a secondary database (DB2) which is then linking to tables in a storage database (DB3).

Does anyone have any ideas as how to solve this issue?

With additional development it would be possible for me to have the all the data in one table (with potentially 100,000’s records) and then only need to link to it once. However, this would result in a loss of performance as my SQL queries would take far longer to extract the data because they would contain more “WHERE” clauses and would need to go through all the records finding the relevant data. Therefore, this is hopefully not a viable option.

 

[Galaxiom]

Ensure all fields used in the where clauses are indexed. This is essential for speed.

Ensure the Where Clauses are efficient.
For example when returning records in the current year, this:

WHERE datefield BETWEEN DateSerial(Year(Date()), 1, 1) AND DateSerial(Year(Date()), 12, 31)

is vastly more efficient than:

WHERE Year(Datefield) = Year(Date())

This is because the second clause must apply a function to every record. The first clause applies only four function calls and can select records based on the index of the datefield.

100K records is not a large database by any means. I have one with ten million records and it returns records selected by indexed fields in a couple of seconds.

 

[Inspired]

Thanks, that didn’t really solve the problem but that’s a useful piece of information. However, intuitively I would have thought that using YEAR - as opposed to DATESERIAL - would be more efficient. What do you mean by function call though as I didn’t really understand your explanation?

 

[Galaxiom]

Thanks, that didn’t really solve the problem but that’s a useful piece of information.

The real problem is dynamically linking to multiple tables because you think it is more efficient and the 100K records is a problem for Access. It is anything but efficient.

Put your data into one table and make sure any fields that are used for criteria are indexed. It makes a vast difference to the performance.

If when a table gets too big for Access (2GB total size of the database is the limit) you would move it to SQL Server Express. The 2008 version can handle 10GB and it is free from Microsoft.

However, intuitively I would have thought that using YEAR - as opposed to DATESERIAL - would be more efficient. What do you mean by function call though as I didn’t really understand your explanation?

When you compare by using the Year function it must be applied to the date field in every record in the table to return a value that can be compared with the Year of the current date.

When the range of dates is calculated as the Where the functions are only applied to the criteria. The raw date in the field can then be compared directly with the date range without any processing of the field values at all.

If the datefield is indexed (as date fields almost invariably should be) then the index can be compared with the Where date range and the matching records retrieved almost instantly.

It is important to realise the difference between brevity of code and the processes they instigate.

An excellent case to consider is the Domain functions. Basically they perform a query every time they are called. Lured by their relatively simple syntax (once you get your head around it) and versatility, many new developers overuse them. Only when they find their forms are taking thirty seconds to load do they realise that populating mutltiple textboxes each with a DLookup is not such a good idea.

 

[Inspired]

The real problem is dynamically linking to multiple tables because you think it is more efficient and the 100K records is a problem for Access. It is anything but efficient.
Put your data into one table and make sure any fields that are used for criteria are indexed. It makes a vast difference to the performance.

If when a table gets too big for Access (2GB total size of the database is the limit) you would move it to SQL Server Express. The 2008 version can handle 10GB and it is free from Microsoft.

Thanks for your reply. With regards to Access limitations and SQL Server, I refer to a previous thread of mine which, as it happens, you also posted on.

http://www.access-programmers.co.uk/forums/showthread.php?t=205226

When you compare by using the Year function it must be applied to the date field in every record in the table to return a value that can be compared with the Year of the current date.

When the range of dates is calculated as the Where the functions are only applied to the criteria. The raw date in the field can then be compared directly with the date range without any processing of the field values at all.

If the datefield is indexed (as date fields almost invariably should be) then the index can be compared with the Where date range and the matching records retrieved almost instantly.

It is important to realise the difference between brevity of code and the processes they instigate.

An excellent case to consider is the Domain functions. Basically they perform a query every time they are called. Lured by their relatively simple syntax (once you get your head around it) and versatility, many new developers overuse them. Only when they find their forms are taking thirty seconds to load do they realise that populating mutltiple textboxes each with a DLookup is not such a good idea.

Ah, that makes sense now, previously I had always been more inclined to join tables etc on MONTH & YEAR but now I'll definitely use DATESERIAL.

 

[Galaxiom]

Thanks for your reply. With regards to Access limitations and SQL Server, I refer to a previous thread of mine which, as it happens, you also posted on.

Man you are really stuck between a rock and a hard place.