UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Son,Father,Grandfather    
 
   
jon_a
post Sep 15 2008, 05:35 AM
Post#1



Posts: 389
Joined: 17-September 02
From: Gateshead. ENGLAND


I have four tables within my DB, first of which is linked to a CSV file, on the click of a button I need to copy this data into my child table ath the same time i wish to backup the father table to the grandfather table and the child table to the father table.
The reasons behind this are, the csv is constantly changing and I need to take a snapshot in time of the csv, I also wish to keep track of the previous 2 snapshots.
Is there an easy way to delete the contents of a table and replace it with the new data from a diffrent table.
i.e. I need to delete all data from grandfather table and the filling it up with the data from the father table.
Go to the top of the page
 
LPurvis
post Sep 15 2008, 05:49 AM
Post#2


UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)


Well - it's "north east" day today ain't it? :-)
ppend queries. Have you used them before?
INSERT INTO tblParent (Field1, Field2, Field3) SELECT Field1, Field2, Field3 FROM tblChild
Obviously preceeded with a cleanout of the target table...
DELETE FROM tblParent
However the large scale, sweeping changes you're making would probably want to be wrapped in a transaction incase, for whatever reason, one of the latter queries failed - to leave yourself in a consistent state.
How many rows in total are we talking about?
Go to the top of the page
 
jon_a
post Sep 15 2008, 06:12 AM
Post#3



Posts: 389
Joined: 17-September 02
From: Gateshead. ENGLAND


around about 12,000 and increasing
Go to the top of the page
 
jon_a
post Sep 15 2008, 06:23 AM
Post#4



Posts: 389
Joined: 17-September 02
From: Gateshead. ENGLAND


Also I need these to run from a button click on the form and I do not know how to run an sql from a button click.
Go to the top of the page
 
LPurvis
post Sep 15 2008, 06:23 AM
Post#5


UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)


A single, default lock size transaction should be able to cope with that no problem.
It's just a question of your field use.
You could go one of several ways... and check for success or failure in several ways.
But for example - something like...
For example...
CODE
    Dim lngRecs As Long
    
    With CurrentProject.Connection
        'Begin the transaction
        .Execute "BEGIN TRANSACTION"
        'Execute your statements
        .Execute "DELETE FROM tblGrandParent"
        .Execute "INSERT INTO tblGrandParent (Field1, Field2, Field3) SELECT Field1, Field2, Field3 FROM tblParent", lngRecs
        If lngRecs = 0 Then
            .Execute "ROLLBACK TRANSACTION"
            GoTo exitErr
        End If
        .Execute "DELETE FROM tblParent"
        .Execute "INSERT INTO tblParent (Field1, Field2, Field3) SELECT Field1, Field2, Field3 FROM tblChild"
        If lngRecs = 0 Then
            .Execute "ROLLBACK TRANSACTION"
            GoTo exitErr
        End If
        .Execute "COMMIT TRANSACTION"
    End With
    
    MsgBox "Append complete"
    Exit Sub
    
exitErr:
    MsgBox "Error encountered - rolled back"
Go to the top of the page
 
jwhite
post Sep 15 2008, 06:32 AM
Post#6


UtterAccess VIP
Posts: 5,804
Joined: 31-August 06
From: North Carolina, USA


Please pardon the difference of opinion...
se just one table -- wiping out the contents before the next import, and save the copies of the previous .csv files imported. If there ever is a need to recover, provide an option to re-import. You could rename the imported .csv before moving it to an "Imported" folder, say "xxx_Imported_20090915.csv
Go to the top of the page
 
jon_a
post Sep 15 2008, 07:28 AM
Post#7



Posts: 389
Joined: 17-September 02
From: Gateshead. ENGLAND


Thanks for that its just what i needed.
Go to the top of the page
 
jon_a
post Sep 15 2008, 07:52 AM
Post#8



Posts: 389
Joined: 17-September 02
From: Gateshead. ENGLAND


Hi john thanks for the reply. I hadn't seen yor reply prior to now. I had considered that as an option.
Go to the top of the page
 
LPurvis
post Sep 15 2008, 08:37 AM
Post#9


UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)


I'm not sure who you're thanking there...
And I'm not sure we know about about what you're actually modelling to know what the appropriate structure is for you.
eel free to elaborate and receive advice in that capacity - as I made no effort to address that. I offered merely a functionality perspective.
Go to the top of the page
 
jon_a
post Sep 16 2008, 04:19 AM
Post#10



Posts: 389
Joined: 17-September 02
From: Gateshead. ENGLAND


My appologies I was thanking you, Leigh, for your soloution to my problem which was what I needed. The three tables allow me to report on the last 2 sets of imported data and the current imported data.
had previousley considered using the idea John by using dated CSV files, but the would make reporting slightly harder.
one question i would like to as is when using the .execute statement how would i word it so it copied email addresses from tblFather into tblChild where tblFather has an email address and tblChild Does not.
INSERT INTO tblESRData ( email )
SELECT tblESRFather.email
FROM tblESRData INNER JOIN tblESRFather ON tblESRData.login = tblESRFather.login
WHERE (((tblESRFather.email) Is Not Null And IsNull(tblESRData.email)))
would this be correct?
Go to the top of the page
 
LPurvis
post Sep 16 2008, 06:41 AM
Post#11


UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)


With relatinships (Parent Child) such as this - it's often possible (and common) to model the relationship with a single table. But that may or may not be your scenario. It's hard to say.
For a "throwaway" reporting type application it's hard to say whether I'd look to import every time.
It's quite likely as the result would be faster data reads. But if you're doing nothing involved with the data then that shouldn't be a problem.
Your query - well, perhaps a bit more explanation is required.
The Login field. This is some form of Unique identifer?
Would the Login be present in both tables - regardless if the email address was entered or not?
If not - then you're definitely wanting an Outer Join...
INSERT INTO tblESRData ( email )
SELECT tblESRFather.email
FROM tblESRData RIGHT JOIN tblESRFather ON tblESRData.login = tblESRFather.login
WHERE tblESRData.login Is Null
A brief comment - you'd used both "Is Not Null" and the function "IsNull" in your criteria line.
While they might well appear to be much the same beast they're not.
IsNull is a built in VBA Function. So the function is called for each value of email passed to it.
This adds processing overhead - and precludes Index optimisation on the passed field.
So in your queries example - you'd have ideally used
WHERE tblESRFather.email Is Not Null And tblESRData.email Is Null.
It's a seemingly small point - but if you're writing SQL then I think it's very much worth knowing.
Cheers.
Go to the top of the page
 
jon_a
post Sep 16 2008, 07:43 AM
Post#12



Posts: 389
Joined: 17-September 02
From: Gateshead. ENGLAND


Thanks for your time Leigh
On theory there should be more login's in tblESRData than in tblFather. There is a problem at the moment with email addresses not being supplied in the new data, hence why I want to copy email addresses from tblFather into tblESRData. But in the near future email address will start being added to tblESRData.
at the moment i wish to update email addresses in tblESRData with email addresses in tblFather where a record in tblESRdata matches a record in tblFather and the record in tblESRData email is a null value and the record in tblFather has an email address.
I think this is clear(as mud)
Go to the top of the page
 
LPurvis
post Sep 16 2008, 07:44 AM
Post#13


UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)


Fair enough - then just compare on Email address being Null.
The Outer Join should still be valid.
.e. you'd have the query I suggested ending with
...
WHERE tblESRData.email Is Null
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 11:28 PM