Results 1 to 3 of 3
  1. #1
    Dobbinsson
    Guest

    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 LineTest
    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
    With CurConn
    .Provider = "microsoft.jet.oledb.4.0"
    .ConnectionString = "data source= TimeChange.mdb"
    .Open
    End With
    Set rst = New ADODB.Recordset
    rst.CursorType = adOpenDynamic
    rst.LockType = adLockOptimistic
    rst.Open "TIMECHANGE", CurConn, , , adCmdTable
    With rst
    .Addnew
    ![Engine] = Engine
    ![wuc] = wuc
    End With
    End If
    Loop
    Close #1
    End Sub

    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.

    Thanks...Richard

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    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

  3. #3
    Dobbinsson
    Guest

    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.

    Thanks Again...Richard

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •