By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,212 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Version/Sequence control in Access 2003 table

P: 4
Iím in the process of setting a sample local Access table called "Fruits" to store data in the following sample fileds:
Week
Amount
Description
Version or sequence

The data will be loaded into the MS Access 2003 table from MS Excel 2003 spreadsheets via VBA code. Iím looking for recommendations on how to advance the version number as data is loaded into the table. For example, on day 1 we would load the following records:

Week/Amount/Description
28/15/Apples
28/20/Pears

On day 2 then, we would like to update this by adding a new set of records to the existing table

Week/Amount/Description
28/10/Apples
28/24/Pears

So the data in the table would look like this

Week/Amount/Description/Version or Sequence
28/15/Apples/1
28/20/Pears/1
28/10/Apples/2
28/24/Pears/2

Iím not sure if I should build some code into the table to advance version/sequence number or if the version should be assigned in the VBA code during the load process. I hope this makes sense, thanks for the help

The sample load code is below:
Expand|Select|Wrap|Line Numbers
  1. Sub Upload_Data()
  2.  
  3. 'uses Microsoft DAO object Library
  4.  
  5. Dim db As Database
  6. Dim rs As Recordset
  7. Dim r As Long
  8.  
  9.  
  10.     Set db = OpenDatabase("Path to database")
  11.     ' open the database
  12.     Set rs = db.OpenRecordset("Fruits", dbOpenTable)
  13.     ' get all records in a table
  14.     r = 2 ' the start row in the worksheet
  15.     Do While Len(Range("A" & r).Formula) > 0
  16.     ' repeat until first empty cell in column A
  17.         With rs
  18.             .AddNew ' create a new record
  19.             ' add values to each field in the record
  20.             .Fields("Week") = Range("A" & r).Value
  21.             .Fields("Amount") = Range("B" & r).Value
  22.             .Fields("Description") = Range("C" & r).Value
  23.  
  24.             .Update ' stores the new record
  25.         End With
  26.         r = r + 1 ' next row
  27.     Loop
  28.     rs.Close
  29.  
  30.  
  31. MsgBox r - 2 & " Records Uploaded"
  32.  
  33. End Sub
Aug 7 '08 #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,186
I can't see why your code is trying to reference Excell cells when it has no access to any Excel objects :S

However, as far as your question is concerned, I would execute a DMax() command at the start of your code which finds the maximum Version or Sequence value used to date, then add 1 to this value and use it for the new session.

Does that make sense?
Aug 7 '08 #2

P: 4
I can't see why your code is trying to reference Excell cells when it has no access to any Excel objects :S

However, as far as your question is concerned, I would execute a DMax() command at the start of your code which finds the maximum Version or Sequence value used to date, then add 1 to this value and use it for the new session.

Does that make sense?

Thanks NeoPa,
I probably wasn't clear that the records would be coming from an Excel sheet and loaded into an Access table. I was hoping that Access could assign the version number based upon the records already in the table when a user loads the new records.
Please correct me if I'm on the wrong path but I haven't used Dmax command before. Would I need to run a SQL query to find the highest version number with Dmax from Excel and pass the value as the version field during the load process? Would the result of this query be a single integer or a recordset with many of the same version numbers? Please let me know if I'm making this too complicated, thanks again for the help
Aug 7 '08 #3

ADezii
Expert 5K+
P: 8,599
You could:
  1. Load the Excel Data into an Access Table, don't worry aboout the Version Numbers now.
  2. Seed the 1st Version Number for each fruit, namely, Apples, Pears, etc. as they initially appear in the Table.
  3. Run an Update Query to Update the Version Fields Where [Version] <> 1, something similar to:
    Expand|Select|Wrap|Line Numbers
    1. 'Next Apples Version Number: ==> 
    2. DMax("[Version]", "<YourTableName>", "[Description] = 'Apples'") + 1
    Expand|Select|Wrap|Line Numbers
    1. 'Next Pears Version Number: ==> 
    2. DMax("[Version]", "<YourTableName>", "[Description] = 'Pears'") + 1
  4. Let me know how you make out.
Aug 7 '08 #4

P: 4
Thanks ADezii,
The update query worked. I only had to change was the where criteria to "is null" from "<>1" and moved the description criteria down to the where line. Thanks again
Aug 8 '08 #5

NeoPa
Expert Mod 15k+
P: 31,186
Thanks ADezii,
The update query worked. I only had to change was the where criteria to "is null" from "<>1" and moved the description criteria down to the where line. Thanks again
Can i assume from this that all your problems are resolved and you have no outstanding questions?
Aug 8 '08 #6

P: 4
NeoPa,

Thanks for checking back. I'm testing a code to load the records from Excel to the table without the version numbers and then run a update query with the dmax function on the recordset. Thanks again for the help
Aug 8 '08 #7

NeoPa
Expert Mod 15k+
P: 31,186
No worries Akos.

Welcome to Bytes :)
Aug 8 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.