Hi Souchie40,
I think I have a partial solution for you. I believe it fulfills the first
part of your request to identify the number of records affected, but it does
not yet include the second part, which read: "preferably with their
titles/unique identifier, or some easy way to identify these records."
You need to add a reference to the "Microsoft DAO 3.6 Object Library", in
Access 2000 or later, in order for this code to run properly. Here is the
modified procedure:
'************BEGIN CODE***********************
Option Compare Database
Option Explicit
Function Import_Data()
On Error GoTo ProcError
Dim db As DAO.Database
Dim strMsg1 As String
Dim strMsg2 As String
Dim strMsg3 As String
Dim lngRecDeleted As Long
Dim lngRecAppended As Long
Dim lngRecUpdated As Long
Set db = CurrentDb
DoCmd.DeleteObject acTable, "Planned Procurement1"
DoCmd.TransferDatabase acLink, "Microsoft Access", _
"F:\Warship Forecast Trimmed.mdb", _
acTable, "Planned Procurement", _
"Planned Procurement1", False
With db
.Execute "Delete Planned Procurement", dbFailOnError
strMsg1 = "Records deleted from Planned Procurement: "
lngRecDeleted = .RecordsAffected
.Execute "AppendPlanned Procurement", dbFailOnError
strMsg2 = "Records appended from Planned Procurement1: "
lngRecAppended = .RecordsAffected
.Execute "UpdatePlanned Procurement", dbFailOnError
strMsg3 = "Records updated: "
lngRecUpdated = .RecordsAffected
End With
MsgBox strMsg1 & lngRecDeleted & vbCrLf _
& strMsg2 & lngRecAppended & vbCrLf _
& strMsg3 & lngRecUpdated, vbInformation, _
"The following operations were completed..."
ExitProc:
'Cleanup
On Error Resume Next
db.Close: Set db = Nothing
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Import_Data..."
Resume ExitProc
Resume
End Function
'************END CODE***********************
I will say that your update query is one nasty looking SQL statement!
Actually, I have some comments after having looked at that.
1.) I encourage you to avoid the use of special characters in anything that
you assign a name to in Access. Here is a KB article on this topic:
Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763
You have field names that include spaces, # signs, parentheses ( ), hyphen
(-), and $.
2.) Your table design is not correct. You have repeating groups of data.
For example:
Gas Turbine
No of Gas turbines
Unit Value GT
GT Classification
and
Gas Turbine 2
No of Gas Turbines 2
Unit Value GT2
GT2 Classification
Also, the C02 to C29 fields (28 total) and 10TOT to 25Tot fields (5 total)
represent repeating groups of data. The field names with xxTot almost suggest
"total". If these fields are being used to store values that can be
calculated from other fields, then this is generally not considered good
database design. I encourage you to read the following two database design
documents, written by Michael Hernandez:
http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf
http://www.datadynamicsnw.com/accesssig/downloads.htm
(See the last download titled "Understanding Normalization")
If you are willing to send me a copy of your database, I can look into
possibly satisfying the rest of your request.
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
:
Thanks very much Tom here is the additional info
Function Import_Data()
' Import_Data
On Error GoTo Import_Data_Err
DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, "Planned Procurement1"
DoCmd.TransferDatabase acLink, "Microsoft Access", "F:\Warship Forecast
Trimmed.mdb", acTable, "Planned Procurement", "Planned Procurement1", False
DoCmd.OpenQuery "Delete Planned Procurement", acNormal, acEdit
DoCmd.OpenQuery "AppendPlanned Procurement", acNormal, acEdit
DoCmd.OpenQuery "UpdatePlanned Procurement", acNormal, acEdit
DoCmd.SetWarnings True
Import_Data_Exit:
Exit Function
Import_Data_Err:
MsgBox Error$
Resume Import_Data_Exit
End Function
The SQL Statements are rather long because of the amount of info but here is
the staement for the update query
UPDATE [Planned Procurement] INNER JOIN [Planned Procurement1] ON [Planned
Procurement].ID = [Planned Procurement1].ID SET [Planned Procurement].Country
= [Planned Procurement1].[Country], [Planned Procurement].[Country Short
Code] = [Planned Procurement1].[Country Short Code], [Planned
Procurement].Updated = [Planned Procurement1].[Updated], [Planned
Procurement].Projection = [Planned Procurement1].[Projection], [Planned
Procurement].Prime = [Planned Procurement1].[Prime], [Planned
Procurement].[Ship Class] = [Planned Procurement1].[Ship Class], [Planned
Procurement].[Vessel Type] = [Planned Procurement1].[Vessel Type], [Planned
Procurement].[Vessel Sub-Type] = [Planned Procurement1].[Vessel Sub-Type],
[Planned Procurement].[Second Hand] = [Planned Procurement1].[Second Hand],
[Planned Procurement].Replacing = [Planned Procurement1].[Replacing],
[Planned Procurement].[US$M Value per vessel] = [Planned Procurement1].[US$M
Value per Vessel], [Planned Procurement].Arrangement = [Planned
Procurement1].[Arrangement], [Planned Procurement].[Gas Turbine] = [Planned
Procurement1].[Gas Turbine], [Planned Procurement].[No of Gas turbines] =
[Planned Procurement1].[No of Gas Turbines], [Planned Procurement].[Unit
Value GT] = [Planned Procurement1].[Unit Value GT], [Planned Procurement].[GT
Classification] = [Planned Procurement1].[GT Classification], [Planned
Procurement].[Gas Turbine 2] = [Planned Procurement1].[Gas Turbine 2],
[Planned Procurement].[No of Gas Turbines 2] = [Planned Procurement1].[No of
Gas Turbines 2], [Planned Procurement].[Unit Value GT2] = [Planned
Procurement1].[Unit Value GT2], [Planned Procurement].[GT2 Classification] =
[Planned Procurement1].[GT2 Classification], [Planned Procurement].Diesel =
[Planned Procurement1].[Diesel], [Planned Procurement].[No of Diesels] =
[Planned Procurement1].[No of Diesels], [Planned Procurement].[Unit Value
Diesel] = [Planned Procurement1].[Unit Value Diesel], [Planned
Procurement].[Diesel Classification] = [Planned Procurement1].[Diesel
Classification], [Planned Procurement].[Nuclear Plant] = [Planned
Procurement1].[Nuclear Plant], [Planned Procurement].[Value Nuclear Plant] =
[Planned Procurement1].[Value Nuclear Plant], [Planned Procurement].[No Of
Nuclear Plants] = [Planned Procurement1].[No of Nuclear Plants], [Planned
Procurement].[Nuclear Classification] = [Planned Procurement1].[Nuclear
Classification], [Planned Procurement].[No Of Shafts] = [Planned
Procurement1].[No of Shafts], [Planned Procurement].[Propulsor Manufacturer]
= [Planned Procurement1].[Propulsor Manufacturer], [Planned
Procurement].Propulsor = [Planned Procurement1].[Propulsor], [Planned
Procurement].[Propulsor Variant] = [Planned Procurement1].[Propulsor
Variant], [Planned Procurement].C02 = [Planned Procurement1].[C02], [Planned
Procurement].C03 = [Planned Procurement1].[C03], [Planned Procurement].C04 =
[Planned Procurement1].[C04], [Planned Procurement].C05 = [Planned
Procurement1].[C05], [Planned Procurement].C06 = [Planned
Procurement1].[C06], [Planned Procurement].C07 = [Planned
Procurement1].[C07], [Planned Procurement].C08 = [Planned
Procurement1].[C08], [Planned Procurement].C09 = [Planned
Procurement1].[C09], [Planned Procurement].C10 = [Planned
Procurement1].[C10], [Planned Procurement].C11 = [Planned
Procurement1].[C11], [Planned Procurement].C12 = [Planned
Procurement1].[C12], [Planned Procurement].C13 = [Planned
Procurement1].[C13], [Planned Procurement].C14 = [Planned
Procurement1].[C14], [Planned Procurement].C15 = [Planned
Procurement1].[C15], [Planned Procurement].C16 = [Planned
Procurement1].[C16], [Planned Procurement].C17 = [Planned
Procurement1].[C17], [Planned Procurement].C18 = [Planned
Procurement1].[C18], [Planned Procurement].C19 = [Planned
Procurement1].[C19], [Planned Procurement].C20 = [Planned
Procurement1].[C20], [Planned Procurement].C21 = [Planned
Procurement1].[C21], [Planned Procurement].C22 = [Planned
Procurement1].[C22], [Planned Procurement].C23 = [Planned
Procurement1].[C23], [Planned Procurement].C24 = [Planned
Procurement1].[C24], [Planned Procurement].C25 = [Planned
Procurement1].[C25], [Planned Procurement].C26 = [Planned
Procurement1].[C26], [Planned Procurement].C27 = [Planned
Procurement1].[C27], [Planned Procurement].C28 = [Planned
Procurement1].[C28], [Planned Procurement].C29 = [Planned
Procurement1].[C29], [Planned Procurement].[10TOT] = [Planned
Procurement1].[10TOT], [Planned Procurement].[15Tot] = [Planned
Procurement1].[15Tot], [Planned Procurement].[19 29Tot] = [Planned
Procurement1].[19 29Tot], [Planned Procurement].[20Tot] = [Planned
Procurement1].[20Tot], [Planned Procurement].[25Tot] = [Planned
Procurement1].[25Tot], [Planned Procurement].Notes = [Planned
Procurement1].[Notes], [Planned Procurement].[Concept Start] = [Planned
Procurement1].[Concept Start], [Planned Procurement].[Concept Select] =
[Planned Procurement1].[Concept Select], [Planned Procurement].[Design Start]
= [Planned Procurement1].[Design Start], [Planned Procurement].[FOC-RFP] =
[Planned Procurement1].[FOC-RFP], [Planned Procurement].[Expect Award] =
[Planned Procurement1].[Expect Award], [Planned Procurement].[Actual Award] =
[Planned Procurement1].[Actual Award], [Planned Procurement].[Length (M)] =
[Planned Procurement1].[Length (M)], [Planned Procurement].[Beam (M)] =
[Planned Procurement1].[Beam (M)], [Planned Procurement].Displacement =
[Planned Procurement1].[Displacement], [Planned Procurement].[Speed (Kn)] =
[Planned Procurement1].[Speed (Kn)], [Planned Procurement].[Range (M)] =
[Planned Procurement1].[Range (M)], [Planned Procurement].[Prospect No] =
[Planned Procurement1].[Prospect No];
The other 2 queries use the same info as above but for delete and append
Planned procurement1 is the source and planned procurement is the destination
TIA
Souchie 40
:
In order for anyone to provide an answer, we'll need a bit more information.
You can start by posting the VBA procedure that you mentioned. Also, post the
name of all queries used, along with their SQL statements. To view the SQL
statement, open the query in design view. Then click on View > SQL View. Copy
the contents and paste it into a new message. Do this for each query, clearly
identifying the name of each query above the SQL statement. Then, maybe we'll
have enough to go on.
I'm calling it a night right now, but I'll continue to monitor this thread
tomorrow evening.
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
:
Hi everyone,
Hope some kind hearted person can help me?
I have a DB that takes info from a table in another DB, this is done by a
macro converted into VBA using a combination of Append, Update and Delete
queries, what I would like to happen is on completion of importing the data a
dialogue box is opened informing the user of the number of records inserted,
deleted and updated, peferably with their titles/unique identifier, or some
easy way to identify these records.
TIA
Souchie40
