Importing Text Data using ADO

[jfarrell]

I'm importing a large text file as a class project and the
requirement is that we use ADO. I can get this code to work
fine in DAO but not in ADO. If anyone can critique this
code and tell me what I'm doing wrong I would appreciate it.
The text file is fixed width and I'm trying to import it into Access97.

Option Explicit
Dim adoimport As New ADODB.Connection
Dim acomimport As New ADODB.Command
Dim rsImport As New ADODB.Recordset

Private Sub Form_Load()
adoimport.Mode = adModeShareDenyNone
adoimport.CursorLocation = adUseClient
adoimport.Provider = "Microsoft.Jet.OLEDB.3.51;Persist_ _Security Info=False;Data Source=C:\project\student.mdb"
adoimport.Open
Set acomimport.ActiveConnection = adoimport
acomimport.CommandType = adCmdTable
acomimport.CommandText = "StarImport"
rsImport.LockType = adLockOptimistic
rsImport.CursorLocation = adUseClient
rsImport.CursorType = adOpenKeyset
End Sub

Private Sub cmdImport_Click()
Dim myfile As String
Dim sLname As String
Dim sFname As String
Dim sMi As String
Dim sStudentID As String
Dim sDOB As Date
Dim sGender As String
Dim sRec As String 'name of the text file string

myfile = gFileName 'gFileName from filelistbox

Open myfile For Input As #1 Len = 2500

Do While Not EOF(1)
On Error Resume Next
'Start the Input of files
Input #1, sRec

sLname = Mid$(sRec, 3, 11)
sFname = Mid$(sRec, 14, 9)
sMi = Mid$(sRec, 23, 1)
sStudentID = Mid$(sRec, 24, 9)
sDOB = Mid$(sRec, 50, 2) & "/" & Mid$(sRec, 46, 2) & "/" & Mid$(sRec, 48, 2)
sGender = Mid$(sRec, 57, 1)

With rsImport
.AddNew

.Fields("Lname&quot = sLname
.Fields("Fname&quot = sFname
.Fields("MI&quot = sMi
.Fields("Studentid&quot = sStudentID
.Fields("DOB&quot = sDOB
.Fields("Gender&quot = sGender
rsImport.Update
End With
Loop

Close #1
MsgBox "Data files Imported Successfully"

End Sub

 

[VBmim]

Option Explicit
Dim adoimport As New ADODB.Connection
Dim acomimport As New ADODB.Command
Dim rsImport As New ADODB.Recordset

Private Sub Form_Load()
adoimport.Mode = adModeShareDenyNone
adoimport.CursorLocation = adUseClient
adoimport.Provider = "Microsoft.Jet.OLEDB.3.51;Persist_ _Security Info=False;Data Source=C:\project\student.mdb"
adoimport.Open
Set acomimport.ActiveConnection = adoimport
acomimport.CommandType = adCmdTable
acomimport.CommandText = "StarImport"
rsImport.LockType = adLockOptimistic
rsImport.CursorLocation = adUseClient
rsImport.CursorType = adOpenKeyset
End Sub

I think that when you use adUseClient for cursorlocation the only cursortype supported is adOpenStatic

hope this clears out a little

 

[reidfl]

Need to know the symptoms.

What problem or error are you getting?



reidfl