Results 1 to 3 of 3
Thread: Help with data import
2001-05-25, 00:48 #1DobbinssonGuest
Help with data import
Okay I am trying to write some code to import some data into a Access table and I have a few problems.
First here is the code:
Private Sub Update_DB_Click()
Dim InputData As String
Dim Engine As String
Dim check As String
Dim wuc As String
Dim CurConn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Open "C:cemskunsan.doc" For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Check for end offile.
Line Input #1, InputData ' Read line of data.
check = Mid$(InputData, 27, 2)
wuc = Mid$(InputData, 22, 5)
LineTest = Mid$(InputData, 22, 2)
if check = "50" then
Engine = Mid(InputData, 29, 4)
If LineTest = "27" Then
Set CurConn = New ADODB.Connection
.Provider = "microsoft.jet.oledb.4.0"
.ConnectionString = "data source= TimeChange.mdb"
Set rst = New ADODB.Recordset
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open "TIMECHANGE", CurConn, , , adCmdTable
![Engine] = Engine
![wuc] = wuc
Problem 1: when I run this it locks up the file and I am not sure why.
Problem 2: I want to be able to look at the value of check and if its 50 then I want to take the engine vale then plug it into the field for engine until the value of check become 50 again and this one is totally baffling me.
Any help would be mucho appreciated.
2001-05-25, 05:38 #2
- Join Date
- Dec 2000
- Sacramento, California, USA
- Thanked 1 Time in 1 Post
Re: Help with data import
A couple of things to clean up:
1. You don't need to both dim an object using the New keyword and then set the object variable using the New keyword (Set CurConn = New ADODB.Connection). In effect, you took care of that in the dim statement. I prefer to NOT use the New keyword in the dim statement because then you haven't actually used the resources until you get to the Set statement. Also, be sure you destroy the connection and recordset objects at the end of your routine. It returns the allocated resources to your application and is good programming practice. In ADO, recordset objects can be especially persistent, so make a habit of explicitly destroying them when you no longer need them.
2. Which file are you talking about locking up? The I/O file or the database file? I'm assuming it's the application because you're making the mistake of setting the connection and recordset objects INSIDE a Do While Loop, which means you're opening them over and over without ever closing them, when you should do it just once outside the loop. Open the connection and recordset objects, then open the file for input and loop through its records, adding a new record to the table when necessary.Charlotte
2001-05-27, 14:50 #3DobbinssonGuest
Re: Help with data import
Thank You. This is just what I needed to get my program to work. I knew it was something silly like that.