I'm sure many of you have had situations where you needed to get data from an external source into your application but, for a variety of reasons, it wasn't possible to link to the data: It had to be imported. At the same time, you might have needed to do something with that new data, such as:
- Run queries to take the data and write it to multiple existing tables in your application.
- Do validations on the values in the table.
So, you didn't actually require the imported data to exist in its original form after you were done. Although you can import the data into a temporary table and work from there, if the import is something that needs to be done on a regular basis, adding the data to the temporary table and subsequently deleting it quickly leads to bloat in your database.
What you might consider doing in such cases is putting your temporary table into a temporary database. That is, after you're done with the raw imported data, you simply delete the temporary database to minimize the impact on your production database. I'm going to give you all the code you need to create such a temporary database by taking advantage of some Windows API calls (and using yet another MP3-related example).
Setting the scene
Those of you who use Windows Media Player (WMP) as the player on your computer might not be aware there's a method of querying the entries in Media Player. I'm going to create a process that gets the details of all the Audio entries and stores those details in a temporary table in a temporary database, then refreshes the "permanent" table with new information. In the refresh, Access updates entries retrieved earlier with the most up-to-date information and adds new entries.
Conceptually, the code is pretty simple. It includes:
- A function to get the name of the file to use for the temporary database (CreateTempFilename)
- A routine to create a temporary table in the temporary database and create a linked table in the application database to work with that temporary table (CreateTempTableDDL)
- A routine to write data to the temporary table (WMPToAccess)
- Code to update the "permanent" table, based on the data in the temporary table (ProcessDifferences)
The controlling routine ProcessData uses these routines to manage the temporary table and, after all that's complete, to delete the temporary database and remove the linked table:
Sub ProcessData()
Dim strTemporaryFile As String
strTemporaryFile = CreateTempFilename()
If Len(strTemporaryFile) > 0 Then
Call CreateTempTableDDL(strTemporaryFile)
If WMPToAccess() > 0 Then
Call ProcessDifferences
End If
End If
If Len(strTemporaryFile) > 0 Then
Kill strTemporaryFile
End If
If TableExists("WMPLoad") Then
CurrentDb.TableDefs.Delete "WMPLoad"
End If
End Sub