If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Appending 1 record at a time

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #4 (permalink)  
Old
Stuck on my opinions...
 
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #6 (permalink)  
Old
(Making Your Life Easy)
 
Join Date: Feb 2004
Location: New Zealand
Posts: 1,275
Quote:
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
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #13 (permalink)  
Old
Stuck on my opinions...
 
Join Date: Nov 2003
Posts: 1,487
Quote:
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
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
Join Date: Jul 2002
Location: Island of Dots
Posts: 316
Quote:
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?
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On