Import MSAccess Extract(Text File) to MSSQL6.5 via VB6

[byronb99]

How can I import a delimited text file that I've extracted from a MSAccess97 database into a MSSQL6.5 database using Visual Basic 6?

[Johnny101]

You could open the text file, then read it in, one line at a time. For each line that comes back, use the Split function (passing the deliminator) to build an array. use the array to build your insert statement for each line and execute it.
psuedo code:

'Open the text file, name the object Filer
Dim sInput as string
dim sInsert() as string

While Not Filer.EndOfFile

sInput = Filer.Readline

sinsert = Split(sInput,",") 'splits the one string into as many array entries as deliminators

sql = "INSERT INTO YourTable (FieldListing) VALUES ('" & sInsert(1) & "', '" & sInsert(2) & "', etc..)"

'execute it on the connection to sql server.
Wend




Or, since you are going into sql server, the best way would be to use BCP and have sql server bulk-copy it in. You can either build the bcp command in code, then save that one line to a .bat file and then execute that file. Or yuo could set a reference to the Microsoft SQL OLE Library and use the SQL Server object model to call the BCP utility.

If you would like to see some code for the SQL OLE stuff, let me know.

Hope this helps/makes sense,
John

John Pirkey
MCSD
www.ShallowWaterSystems.com

[Spectre]

Why extract from Access to a flat file first? Just open a connection to the Access database - open a connection to the SQL database - then get a recordset from the Access table - iterate through the recordset writing each record to the SQL table - close everything up. If you would like code let me know and I'll e-mail it to you.