Importing Data (Access)

[preeti]

With access there are tools for importing data.

See the menu:File|Get External Data|Import.

With this option you can choose what format the data is in that you want to import (another database, text file) etc...

Follow the instructions or see help files for using this option

[FDR]

Uggg, I was afraid someone would say something like that. I know I'm in way over my head when I know how to do that myself, but I don't know how to make VB do it.

I am so lame. ;-)

[FDR]

I'm building a fairly basic application, it draws it's info off of Access, but here's my problem, the Data get's updated about once a week, and needs to be re-imported. This is kindda' annoyin' if I have to do that step by hand when everything else is nice and streamlined. I'm new to VB, so I'm not sure how to do much more than pull info from the DB. Anyone have any suggestions?

[preeti]

VB can do it. All you need is a for loop and insert or update statement depending on how you want to update your data.

If you are planning to purge all the data then refill it, your last statement will be an insert statement, if not, then you will use an Update statement with a primary key that you know will never change.

Depending on the format that your data is being given to you, the code is very simple.

How is the data being given to you?

As a file, another table etc...

Preeti

[FDR]

I'm getting the data in a Comma delimited text file. It tends to be about 500Kb on average.

I would like to empty out the DB when I start to import the data, which was making me think about using VB to create a new DB every week. Would that be easier to do than to just empty out the old info?

Thanks!

[haveitall]

You could try replicating you database (tools--replication--create replica in Access), then db.synchronise. Email me if you want code.

[preeti]

Recreating the database or purging all the data would be the same thing, it just depends on the time frame. You could try eliminating your database in both ways, and see which way you like better (speed wise, code etc...) I don't have a database to purge so I can't tell you which is better. I prefer though to purge the data only because I have found that vb does some funny things when creating databases.

Anyway, after you have purged your data:

1- Open your file
2- Create variables for the data
3- While not File.EOF
4- Assign variables for each field in file to separate data fields
5- INSERT INTO table
6- Loop

Here's an example using a comma delimited file:

Dim filename As String 'Holds the name of the filename
Dim filenumber As Integer 'Holds the filenumber
Dim gicTable As Recordset 'Object to access the tables in the database
Dim Prm1 As String 'Holds a Parameter in the record from the files
Dim Prm2 As String 'Holds a Parameter in the record from the files
Dim Prm3 As String 'Holds a parameter in the record from the files
Dim Prm4 As String 'Holds a Parameter in the record from the files
Dim Prm5 As Double 'Holds a Parameter in the record from the files
Dim lPrm1 As Long 'Holds a Parameter in the record from the files
Dim lPrm2 As Long 'Holds a Parameter in the record from the files
Dim lPrm3 As Long 'Holds a Parameter in the record from the files
Dim ExtraChars As String 'Picks up the Extra Characters at the end of the file
Dim i As Integer 'Controls the for loop

'Open the first Table: Product
Set gicTable = db.OpenRecordset("tablename", dbOpenTable)
filename = "pathoffile" 'Open the file
filenumber =FreeFile
'Retrieve a filenumber to assign to the file
Open filename For Input As filenumber
'Open file for retrieving data
Do Until EOF(filenumber)
'Do while not-end-of-file gicTable.AddNew
'Add a new record to the table
Input #filenumber, Prm1, Prm2, Prm3, Prm4
'Take record
Line Input #filenumber, ExtraChars
gicTable!field = Prm1
gicTable!field = Prm2
If Prm3 = "X" Then 'Manipulate data
gicTable!field = "*"
Else
gicTable!field = " "
End If
gicTable!field = Parm4 gicTable.Update 'Update the table
Loop 'Do over again
Close filenumber 'Close the file
gicTable.Close 'Close the Table

HTH,

Preeti

[FDR]

Thank you for all of your assistance.

You are like a God to me. ;-)

FDR