Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2007
    Posts
    93

    Unanswered: import into table using recordset

    hello freinds !!

    i am trying to import data into the table using recordset

    it used to work fine but now it luks like its jst importing the first line and exiting .. .its not importing the whole data into the table

    so i am lil confused as its not showing any error ...


    any clues would be apperciated


    thanks

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Please post your code as a starting point.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Nov 2007
    Posts
    93
    as a starting point ??

    i didnt get that

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Starting point - so that we can look at what is happening and what the problem might be.

    I am assuming this is a VBA module which you have a problem with yes?

    If so, please post the actual code of the VBA for us to look at.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    Nov 2007
    Posts
    93

    vba code

    Private Sub Import_Click()
    On Error GoTo Error_ImportItemSales
    Dim strLine As String
    Dim strSalesman As String
    Dim rst As DAO.Recordset
    Dim lngrecord As Long

    If MsgBox("Delete all Item Sales Records", vbQuestion + vbDefaultButton2 + vbYesNo) = vbYes Then
    CurrentDb.Execute ("DELETE * FROM Item_Sales")
    End If

    Set rst = CurrentDb.OpenRecordset("Item_Sales")
    Open CurrentProject.Path & "invsales.txt" For Input As #1 ' Open file for input.

    Do While Not EOF(1) ' Loop until end of file.
    Line Input #1, strLine
    'Debug.Print strLine
    lngrecord = lngrecord + 1
    rst.AddNew
    rst!Invoice_Date = Trim(Left(strLine, 10))
    rst!Order_No = Trim(Mid(strLine, 11, 8))
    rst!Product_Line = Mid(strLine, 19, 3)
    rst!Item_Code = Mid(strLine, 22, 10)
    rst!Category = Mid(strLine, 32, 3)
    rst!Cust_No = Mid(strLine, 35, 8)
    If Trim(Mid(strLine, 43, 2)) <> "" Then rst!Salesman = Mid(strLine, 43, 2)
    rst!Quantity = Mid(strLine, 45, 6)
    rst!Cost_Price = Mid(strLine, 51, 9)
    rst!Sale_Price = Mid(strLine, 60, 9)
    rst!Invoice_No = Mid(strLine, 69, 7)
    rst!Line_No = Mid(strLine, 76, 3)
    rst!Location_No = Mid(strLine, 79, 1)
    If Trim(Mid(strLine, 80, 7)) <> "" Then
    rst!RMA_No = rst!Order_No
    rst!Order_No = Trim(Mid(strLine, 80, 7))
    End If
    rst!Period_Date = DateAdd("m", 1, DateSerial(Year(rst!Invoice_Date), Month(rst!Invoice_Date), 1)) - 1
    rst.Update
    'Debug.Print Trim(Left(strLine, 10))
    Loop

    Close #1 ' Close file.
    MsgBox "Import Completed"
    Exit Sub
    Error_ImportItemSales:
    If Err.Number = 3218 Then
    Err.Clear
    Resume
    End If
    Exit Sub
    End Sub

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Fix your error handler. Then the fault may reveal itself.

    Code:
    Error_ImportItemSales:
       If Err.Number = 3218 Then
          Err.Clear
          Resume
       End If
       Exit Sub
    Code:
    Error_ImportItemSales:
       If Err.Number = 3218 Then
          Err.Clear
          Resume
       Else
          Msgbox Err.Description
          Resume CleanExit
       End If
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Just a note:

    This...
    If MsgBox("Delete all Item Sales Records", vbQuestion + vbDefaultButton2 + vbYesNo) = vbYes Then

    can be re-written like this...

    Dim QI As Integer
    QI = MsgBox("Delete all Item Sales Records", vbQuestion + vbYesNo)
    if QI = vbyes then...
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    To what benefit? I try to do it the former way before doing it the latter way... to save a variable and additional lines of code.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    If it is still not giving you an error, put a breakpoint against each line of code.

    Then follow the sub as it runs through to see how far it actually gets.

    Have you tried with different source .txt files?

    Is there any erroneous data?

    Just a few suggestions...
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  10. #10
    Join Date
    Nov 2007
    Posts
    93

    record set problem

    it isnt showing any error and i am importing txt files

    it goes thru the loop once and exits the operation. . shows nothing ...

    no errors .. when i try to debug .. and setting up break pts... wasn't useful and it imports the first line into the table and simply exit

    but want to bring u at notice that ... if files are uploaded then it shuld prompt for imported completed .. but it doesn't showing even that ..

    wonder were the problem is

    and also one importnt thing is ...

    this line of code is running from other system preety well and only at one system its nt running .. so any clues why its showing so strange behaviour

    any suggestions would be of grt help to me ..

    thanks
    sam

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    See post 6?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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