Take a look at the TransferText Method it might be what your looking for. Also depending on the version of access and options you loaded during install you might have to look at Microsoft Knowledgebase article Q171955.
Here is a whole bunch of code that might/might not help you out. it is pretty well documented, so you can at least see some possibilities. I am not a great programer, so thier is probably better ways to do it. This was behind a button on a form and pulled in a computer doc. HTH Jim
Private Sub btnSansImport_Click()
On Error GoTo btnSansImport_Err
'------------------------------------------------------------
' The following issues need to be resolved in this code:
' 1. The LPOC data thru the SANS people. If they place LPOC data
' into SANS we need to import the data, create a form to display
' during the import to obtain Lat/Long.
' 2. Before production in the 1st module change default value back
' instead of default test name.
' 3. Need to set up the code to splice out the name of the file importing
' to the dateTime of the download and use that time to populate the
' table of data.
'------------------------------------------------------------
'------------------------------------------------------------
' This Code is used to import a SANS .CSV file from the same
' Folder that the database is in. Simply enter SANS and export
' an Excel file to the folder.
'------------------------------------------------------------
On Error Resume Next
DoCmd.DeleteObject acTable, "tblImportedSANSData"
On Error GoTo btnSansImport_Err
Dim tmpErrorImportFileNameToDelete As String
GetFileName = TestIt()
If GetFileName = "" Or IsNull([GetFileName]) Then ' User chose cancel
GoTo btnSansImport_Exit
End If
On Error GoTo btnSansImport_Err
'------------------------------------------------------------
' This Code is used to delete any previous SANS imported data
' from the tblsightings prior to importing any data.
'------------------------------------------------------------
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDeleteOldSANSRecordsDuringNewRecordImport"
DoCmd.SetWarnings True
statusbartext.Height = "2200"
Me.Requery
statusbartext = "IMPORT STATUS:" & vbCrLf & _
"Successfully deleted old SANS import data..."
Me.Refresh
'------------------------------------------------------------
' This Code is used to import the new SANS data into a
' temporary table for further modification.
'------------------------------------------------------------
DoCmd.TransferText acImportDelim, "", "tblImportedSANSData", _
FindPath() & "\" & [GetFileName] & ".csv", True, ""
tmpErrorImportFileNameToDelete = [GetFileName]
Me.Requery
statusbartext = ("Successfully completed import of SANS table...")
Me.Refresh
'------------------------------------------------------------
' This Code resizes the window to accomidate the user messages.
' The user messages have no real meaning other than to assist
' in determining errors.
'------------------------------------------------------------
DoCmd.MoveSize , , , 4575
Me.FormFooter.Height = "2500"
Me.statusbartext.Visible = True
'------------------------------------------------------------
' This Code is used produce a report of error's encountered during the import.
' Specificly, the errors are those records that SANS did not have a
' Arrival_Port or a COTP_Zone listed.
'------------------------------------------------------------
Msg = "Ship movements logged into the SANS system not having an arrival" _
& "port listed will not be imported into this database. Please " _
& "review, and/or print this list before continuing."
Style = vbYesNo + vbExclamation + vbDefaultButton2 ' Define buttons.
Title = "SOME SHIPS WON'T BE IMPORTED" ' Define title.
' context.
' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
DoCmd.OpenReport "rptPrintRecordsBeingDeleted", acViewPreview, , , acDialog
'DoCmd.PrintOut acPrintAll
'DoCmd.Close acReport, "rptPrintRecordsBeingDeleted", acSaveYes
Else ' User chose No.
End If
statusbartext = (statusbartext & vbCrLf & "Successfully completed print routine")
Forms!frmsightinglog.Refresh
'------------------------------------------------------------
' This Code Deletes those records that were identified in the report.
' The records are deleted to prevent errors in the the creation.
'
'------------------------------------------------------------
DoCmd.SetWarnings False
DoCmd.OpenQuery "qrydeleteUnknownEntries"
DoCmd.SetWarnings True
statusbartext = statusbartext & vbCrLf & _
"Successfully deleted records with unknown ports..."
Me.Refresh
'------------------------------------------------------------
' This Code modifies the imported table to match the fields contained
' in the tblSightinhLog which it will be appened to later during this
' sub routine.
'------------------------------------------------------------
Dim tbldefs As TableDefs
Dim tbldef As TableDef
Set tbldefs = CurrentDb.TableDefs
Set tbldef = tbldefs("tblImportedSANSData")
With tbldef
.Fields.Append .CreateField("SightLat", dbText, 10)
.Fields.Append .CreateField("SightLong", dbText, 11)
.Fields.Append .CreateField("dataSource", dbText, 1)
End With
statusbartext = statusbartext & vbCrLf & "Successfully Created Fields..."
Me.Refresh
'------------------------------------------------------------
' This Code changes the data type of the Ship_Number field
' from the default number field- to a text field.
' This calls upon a funtion to do the actual work.
'------------------------------------------------------------
Dim dbs As Database, tdf As TableDef, rst As Recordset
Dim fld1 As Field, fld2 As Field, fld3 As Field, prp As Property
Dim i As Integer
' Return reference to current database.
Set dbs = CurrentDb
' Return reference to target table.
Set tdf = dbs.TableDefs("tblImportedSANSData")
' Reference the field you want to rename.
Set fld2 = tdf![Ship Number]
' Change the field name to what I want
fld2.name = "Ship_Number_2"
'We can not change Field Type or Size, they are read-only.
'fld1.Type = dbText
'fld1.Size = 50
'So we have to create a new field with Type and Size we want.
'Here go!
Set fld3 = tdf.CreateField("Ship Number", dbText, 20)
' Append new Field object.
tdf.Fields.Append fld3
' Refresh Fields collection.
tdf.Fields.Refresh
' Delete new Field object.
tdf.Fields.Refresh
'And it's time to add Caption to the newly created field
Set prp = fld3.CreateProperty("Caption", dbText, "Ship Number")
With fld3
.Properties.Append prp
.Properties.Refresh
End With
'Move data from fld2 to fld3
Set rst = dbs.OpenRecordset("tblImportedSANSData")
rst.MoveFirst
For i = 1 To rst.RecordCount
rst.Edit
'rst(2) = rst(1)
rst![Ship Number] = rst!Ship_Number_2
rst.Update
rst.MoveNext
Next i
Set rst = Nothing
'Delete fld 2
tdf.Fields.Delete fld2.name
Set dbs = Nothing
statusbartext = statusbartext & vbCrLf & _
"Successfully converted ship_Nr to TxtField..."
Me.Refresh
***********************SEE NEXT POST*********************
***********************FULL CODE TOO LONG ****************
? Pls help.
