|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
|
|
#1
November 20th, 2008, 10:20 AM
|
|||
|
|||
|
MS Access - replicate records in a database
I have a table that lists an event with a start date and an end date as one record. I need to replicate the record for the number of days between start and end.
|
|
#2
November 20th, 2008, 12:05 PM
|
||||||
|
||||||
|
Is it the EXACT SAME data every time or does something change?
Are you looking for a query or is VBA code OK?
__________________
P.S. I am looking for work. <grin>.
|
|
#3
November 20th, 2008, 12:44 PM
|
|||
|
|||
|
Quote:
It will different data every time. I would prefer a query but I can't figure out how to replicate the record.
|
|
#4
November 20th, 2008, 05:39 PM
|
||||||
|
||||||
|
Sorry I guess I wasn't clear.
My question is are the duplicate records exactly the same as the original or do you need some sort of different data? Also if the start date is 11/4/2008 and the end date is 11/10/2008 do you want to add 5 or 6 records or some other number of records?
|
|
#5
November 21st, 2008, 06:49 AM
|
|||
|
|||
|
Quote:
Except for the start date the data in the fields of the record would be exactly the same. I need the start date to ascend 1 for each day. If the start date is 11/4/2008 and the end date is 11/10/2008 then I would need to add 6 records as 1 already exists. I can write something to change the start dates for the new records. Thanks.
|
|
#6
November 24th, 2008, 11:34 AM
|
||||||
|
||||||
|
OK here's the basic code for what you want.
Code:
Dim connDB As ADODB.Connection
Dim miscRS As New ADODB.Recordset
Dim qry As New Command
Dim sql As String
Dim fulSQL As String
Dim ndx As Integer
Dim startDT As Date
Dim dtCnt As Integer
Set connDB = CurrentProject.Connection
Set qry.ActiveConnection = connDB
sql = "SELECT * FROM <tblName> "
miscRS.Open sql, connDB, adOpenStatic
sql = "INSERT INTO <tblName> (<field1Name>, <field2Name>, <StartDateField>) VALUES ("
Do While Not miscRS.EOF
startDT = Miscrs!<startDateField>
dtCnt = DateDiff("d", startDT, miscRS!<endDateField>)
For ndx = 1 To dtCnt
fullsql = sql & Miscrs!<field1Name> & "," & miscrs!<field2Name> & ",#" & dateAdd("d",ndx,startDT) & "#)"
qry.CommandText = fullsql
qry.Execute
Next
miscRS.MoveNext
Loop
miscRS.Close
connDB.Close
Set qry = Nothing
Set connDB = Nothing
You will need to replace <name> with the appropriate name from your application. If you need to run this more than once, you will need to add some sort of WHERE clause to the first sql statement to get only the new records you want processed.
|
|
| Viewing: Dev Articles Community Forums > Databases > Microsoft Access Development > MS Access - replicate records in a database |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
