VB4 as an Importing Tool for Access 7.0 
Situation:
I have an Access 7.0 (Win95) database with 3 tables. These tables
contain data collected from a Cellular Phone Switch (a really big PBX).
I parse the data into a comma de-limited text file and then import it
into Access.
Right, now I wrote a program that records the data, parses it, and places the de-limited file into a raw data directory on an NT server. When I arrive to work in the morning, I start Access and run a function that I wrote to import the data into the tables. The function just keeps me from having to walk through the import process step by step.
Problem:
Access appears to not have a "timer" control like VB does.
So I can't write code in Access to run my function at, say, 1:00AM every day.
Besides, it's not necessary to run Access on the server, only from
the workstations. This means a lot of work if I miss a day and have
to go back and manually import the data for all three tables.
Question:
Is there a way to "access" the ".mdb" file and
import my data with VB4? I'm hoping I don't have to actually start
Access, Open the database, call the function, and close Access.
I can write the timer code to do functions at certain times, but I don't know if ".mdb" files can be opened and appended to without actually starting Access. Any ideas, or alternate methods? TIA.
Jeff Motter
Email : jmotter@fgi.net
Yes, what you want to do can be done. This is just the sort of thing that VB is really good at. VB4 in particular.
The JET engine is exposed to Visual Basic 4 almost identically to the way it is exposed to Access. Access Basic and Visual Basic can often be indistinguishable - particularly for Database work. Here is some of the fundamental VB code you may end up using:
Dim dbimport as Database
Dim dsPut as Recordset
Dim iFileNum as Integer
' VB opens the MDB directly using the JET engine
' Access does not need to be run.
Set dbImport = DBEngine.WorkSpaces(0).OpenDatabase("C:\MyDir\Import.mdb")
Set dsPut = dbImport.OpenRecordset("tblImport", dbOpenDynaset)
iFileNum = FreeFile
Open "c:\MyDir\import.txt" for input access read as iFileNum
....
' Read the text file
' write each record into the dynaset...
' and so on...
And so on. Looks very similar to Access code, doesn't it? Once you have a grip on the VB environment, it should only take a little while for anyone with a reasonable knowledge of Access to port the code over - much of it will become a copy and paste exercise.
Have a look in the VB4 manuals, they explain all the DataAccess functionality quite well, and the beginner's sections are reasonable.
Good Luck. Code long and prosper.
