Importing 2 fields from Text File into Access

linuxman
01-11-2004, 10:32 PM
I am trying to import a text file, I have tried everything possible, or I think; 24 hours later I have to call on some advice. I have the following code. It is pulling from a comma separated flat text file and inserting it into an access database. I am wanting to combine the two fields which as FName and LName wanting to place it together into a field in access call employees. I was importing into two fields and then doing the work each time I accessed the data. However, it would be easier to import the fields into one the original time and leave it. I have placed the code I thought would work at the very bottom. Why will that code not work? Any suggestions. As you can see from other post, I tried stripping the commas, but then I end of with a value after the last records that will blow the import out with an error on the last record. I wish I could solve that, but this option may be faster. Thanks again.

PathtoTextFile = App.path & "\EmployeeImport\"
PathtoMDB = App.path


Set Cat = New ADOX.Catalog
Set objTable = New ADOX.Table
Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & PathtoMDB & "\" & "batch.mdb"
'Open the Catalog
Set Cat.ActiveConnection = Cn

‘Used to Delete if wanting to refresh table
If chkDeleteTable.Value = 1 Then
Cat.Tables.Delete "Employee"
End If

objTable.Name = "Employee"

objTable.Columns.Append "employee", adText

Cat.Tables.Append objTable


Cn.Execute "INSERT INTO Employee SELECT * FROM [Text;Database=" & PathtoTextFile & ";HDR=YES].[employee.txt]"

‘Thinking something like this
Cn.Execute "INSERT INTO Employee SELECT FName + LName As employee FROM [Text;Database=" & PathtoTextFile & ";HDR=YES].[employee.txt]"

linuxman
01-11-2004, 11:23 PM
Nevermind I see the error, was using '+' instead of '&' it is now working like a charm.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum