Appending records from a text file to a table. ADO problem!!

[ALSav]

I have a set of records stored as a delimited text file and would like to append them to a table in a database. The idea is that the user will be prompted to open a database and the connection string will be constructed from the choice made. However I always get an error saying that the path is not valid Here's the code.

Private Sub cmdAppend_Click()

Dim connCSV As New ADODB.Connection
Dim rsTest As New ADODB.Recordset
Dim adcomm As New ADODB.Command
Dim path As String

MsgBox "Select database file"
dlgOpen.ShowOpen
path = dlgOpen.filename
MsgBox "path is " & path
If path = "" Then
MsgBox "You have not selected a database"
Exit Sub
Else

connCSV.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& path & ";Extended Properties='text;HDR=NO;FMT=Delimited'"

etc
etc

I'm afraid I'm a bit in the dark when it comes to connection strings etc. Is it possible to add records from a text file??

 

[rtshort]

Not to my limited knowledge. I have not ran across anything in the 6 months I've been learning VB with an accent on databases.

 

[DanX]

If I am understanding correctly, seems to me you have 2 questions here, 1) identifying and opening a database and 2) writing data from the text file to the database.

Re 1), are your users savvy enough to know what and where the databases are? Instead of having them rummage thru the drives for something, better perhaps to show the ones you want them to use in a list box with a description, and based on their selection pick the connection routine. You should test those connections on their own without all the variable path etc. stuff.

Re 2), you need to open the text file, read a line into some variables, (maybe do some validation of some sort to some of them?), then write them to the database using either sql or ado.

 

[Mats]

Just in case you have pasted the code.

Check the string, there seems to be an extra pair of ''.

...Properties='text;HDR=NO;FMT=Delimited'"