Controlling the Size of an Access db created with VB 1

[sdh]

I am just beginning programming in VB and have this problem.

After creating and access db in vb I am importing text files and creating tables using SQL (the create and then insert keywords) the problem is this
takes a long time and the database grows from 239kb empty to 40,989kb with the new data (approx 36,000 records). I have tried compacting using the jet database engines function but it does help.
Does anyone have any ideas.

thanks

 

[jmarler]

How big is the text file that you're importing and are you normalizing the data at all? And as for the time it take for the import, how are you importing? Are you parsing the text file or are you linking the text file to the access database with a custom schema.ini and then treating it as a table (this will be the faster of the 2 methods by an order of magnitude). - Jeff Marler B-)

 

[sdh]

jmarler

I am reading the textfiles with the filesystem object then
splitting the lines according to thier delimiter
whats this ini file method?

 

[sdh]

just read a little about schema.ini my problem is i will not know how big these text files are our how many columns they contain can i get round this?
the test data i am using is 308kb and has eleven columns but this may alter either way so i need to make it as efficient as possible.

 

[jmarler]

With the schema.ini you can use fixed width columns or delimited files . . . either will work. I wrote an app a few years ago and importing by parsing the text file took over 20 minutes . . . linking the text file as a table and then running SQL took under a minute.
You mentioned that the number of columns may change? Why is this? Do you not have a fixed data model? There are ways to have an app dynamically create its own ini file, but it can get a little complicated when you consider all of the possibilities.
Also, were you normalizing your data at all, or is it all going into 1 table inside of Access? - Jeff Marler B-)

 

[sdh]

jmarler

it is all going into one table in the current database created by the app. I am then using sql to join that to other table fields in already present tables.

the column numbers will change because i am importing the data from a dos app. which writes out lines basically each with data separated by a delimiter depending on certain circumstances these can vary substantially. So far i have not seen any longer than 13 columns but i have seen all combinations between 1 and 13 and do not know the app. in question well enough to determine what its limits are.

thanks for the help anything further?

 

[jmarler]

What kind of rules are you currently using in your app when you parse the text file? Can you provide a list or the acutal code? You will need to use that same list of rules to dynamically create you schema file.
Also, I am assuming that the 1 table that you mentioned is simply a temp table that holds the contents of the text file until it is ready to be redistributed . . . if that is the case, then the linked text file will replace that table. - Jeff Marler B-)

 

[sdh]

beginning to really want to use this method now.

been looking at it further myself

my rules are simple i just use the following code to split the line into an array and use that array in my sql statement



d.readline
linarray=split(d,";&quot
I use the ubound(linearray) to determine the number of fields
use a for next loop to insert into appropriate fields (they are determined by reading through the file and find the highest possible ubound(linearray)this does not take long at all)using
sql statement

 

[sdh]

still struggling can anyone help

I wrote a schema.ini file and tried to use it as a recordset
using this code but I get data mismatch on the line numbered

Dim db As Database, rs As Recordset
Set db = OpenDatabase(globaldatabasepath, False, False, _
"Text;DATABASE=j:\canada\setup_data\;TABLE=pchist.txt&quot
1. Set rs = db.OpenRecordset("PChist.txt&quot...this line is where i drop out

rs.MoveLast
Debug.Print "Record Count:" & rs.RecordCount
rs.Close