Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316

    Appending 1 record at a time

    I need to make a change to one of the fields in an Access table (tblJobDetails). However, this table holds nearly 200,000 records, and I get an error from Access when I amend the field length and try to save it (out of memory, or similar). I also tried making a copy of the table (structure only) and running an append query from old to new table, but get the same error. The only solution I can think of is to write a small routine that appends 1 record at a time. However, I'm not entirely sure how to do this, I've not done much with rs.MoveFirst, rs.MoveNext etc. Can anyone help me write this small routine?

    Many thanks in advance.

  2. #2
    Join Date
    Jan 2004
    Posts
    26
    I don't mean to be presumptuous, but perhaps it is time to upgrade to SQL Server. Access gets a little goofy when you start getting in to 6 digit records.


    Just my opinion,
    Scott

  3. #3
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Its a good idea, and we have SQL Server, but I've never done any development with it. I was hoping not to have to learn a whole new system just yet. What I'm proposing is possible isn't it?

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    You might want to try this....

    Code:
    Private Sub ChangeFieldData()
           Dim dbs As Database, rst As Recordset
           Dim strCriteria As String
           Screen.MousePointer = 11  'Change the Mouse pointer to Hourglass.
           Set dbs = CurrentDb
           strCriteria = "[RecordID] <> 0"
           Set rst = dbs.OpenRecordset("tblJobDetails", dbOpenDynaset)
           'Find first matching record.(which in ths case will be all of them)
           rst.FindFirst strCriteria
           'Check if record is found.
           If rst.NoMatch Then
                MsgBox "No record(s) found."
           Else
               'Find other matching records.
               Do Until rst.NoMatch
                    rst.Edit
                    'The line below will change the provided table field data to the new provided data.
                    rst!myFieldName = "myNewValue"
                    rst.Update
                    rst.FindNext strCriteria
               Loop
           End If
           rst.Close
           Set dbs = Nothing
           Screen.MousePointer = 0    'Change the Mouse pointer back to normal.
    End Sub
    Hope it helps

  5. #5
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Thanks for the routine. I think I understand it, but I can't get it to work. What I need to do is to change a field length (JobNumber in tblJobDetails) from 8 to 10. How can I account for that in your code?

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,275
    Originally posted by bcass
    Its a good idea, and we have SQL Server, but I've never done any development with it. I was hoping not to have to learn a whole new system just yet. What I'm proposing is possible isn't it?
    just import the tables in SQL Server

    just watch out for the autonumber feild sql doesn't see it as a autonumber but does get imported have to manually fix it by setting the Increment value

    then just create obdc connettion

    then just link the table in access to the obdc
    (I had to rename each table to old table name and everthing work even faster in some cases)

    you can even link views(query) but access see then as tables

    my database was off line for 6 hours
    and that with no SQL training just jump in and did it
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE

  7. #7
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    The company I work for are a little more stringent. They wouldn't give me access to SQL Server tools unless I has some training. MY only option at the moment is to stick with Access

  8. #8
    Join Date
    Feb 2004
    Posts
    3
    I say stick with your append query, but limit the number of records that are appended to the structure-copied table (make sure after you copy the structure that you go and edit the field restrictions you want to change =).

    For example, if your table had an autonumber field you might do an append query where that field was < 1000. Then next do >= 1000 AND < 2000

    Or some such. This will let you increment the number of appends going on - but I have a hunch that you are still going to have errors.

    kk

  9. #9
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Appending 1 record at a time (manually) works without problem, but with so many records, I need to automate the process. This is possible, its just that I'm not sure how to start coding such a routine.

  10. #10
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    I need to create a recordset based on the source table, then loop through each record, appending each to the new destination table. Loop all this until the end of the recordset is reached. I just don't know how to put this into code.

  11. #11
    Join Date
    Feb 2004
    Posts
    3
    Dim Connection As Object
    Dim rst As Object
    Dim srch As String
    Dim sql As String
    Set Connection = Application.CurrentProject.Connection
    Set rst = CreateObject("ADODB.RecordSet")
    srch = "SELECT * FROM tblJobDetails"
    rst.Open srch, Connection, 1
    Do While rst.EOF = False
    sql="INSERT INTO tblNEW (newfield1, newfield2) VALUES ('" & rst!field1 & "', '" & rst!field2 & "');"
    DoCmd.SetWarnings False
    DoCmd.RunSQL sql
    DoCmd.SetWarnings True
    rst.MoveNext
    Loop
    rst.Close


    I would do something like this, should get you in the right direction. Apologies in advance for typos.

    kk

  12. #12
    Join Date
    Feb 2004
    Posts
    15
    I may be off base here but I recently ran into the same problem when trying to update large numbers of records. I ran accross this windows support article.

    http://support.microsoft.com/default...NoWebContent=1

    The problem as I see it is that windows only locks 9500 records at a time. So when you attempt to change 200,000 records you run into problems. Appearently you can fix it by editing the registry or by using some VBA code. This may be out of date though.

  13. #13
    Join Date
    Nov 2003
    Posts
    1,487
    Originally posted by bcass
    Thanks for the routine. I think I understand it, but I can't get it to work. What I need to do is to change a field length (JobNumber in tblJobDetails) from 8 to 10. How can I account for that in your code?
    Change To:

    Code:
    Private Sub ChangeFieldData()
           Dim dbs As Database, rst As Recordset
           Dim strCriteria As String
           Screen.MousePointer = 11  'Change the Mouse pointer to Hourglass.
           Set dbs = CurrentDb
           strCriteria = "[JobNumber] = 8"
           Set rst = dbs.OpenRecordset("tblJobDetails", dbOpenDynaset)
           'Find first matching record.(which in ths case will be all of them)
           rst.FindFirst strCriteria
           'Check if record is found.
           If rst.NoMatch Then
                MsgBox "No record(s) found."
           Else
               'Find other matching records.
               Do Until rst.NoMatch
                    rst.Edit
                    'The line below will change the provided table field data to the new provided data.
                    rst!JobNumber = 10
                    rst.Update
                    rst.FindNext strCriteria
               Loop
           End If
           rst.Close
           Set dbs = Nothing
           Screen.MousePointer = 0    'Change the Mouse pointer back to normal.
    End Sub
    Hope it helps

  14. #14
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Originally posted by KryptKr4v3r
    Dim Connection As Object
    Dim rst As Object
    Dim srch As String
    Dim sql As String
    Set Connection = Application.CurrentProject.Connection
    Set rst = CreateObject("ADODB.RecordSet")
    srch = "SELECT * FROM tblJobDetails"
    rst.Open srch, Connection, 1
    Do While rst.EOF = False
    sql="INSERT INTO tblNEW (newfield1, newfield2) VALUES ('" & rst!field1 & "', '" & rst!field2 & "');"
    DoCmd.SetWarnings False
    DoCmd.RunSQL sql
    DoCmd.SetWarnings True
    rst.MoveNext
    Loop
    rst.Close
    This seems to work, but I am getting errors whenever the code tries to append an address [Customer Address] with symbols in it, such as ' or ,. Any ideas why?

Posting Permissions

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