RE: Synchrinising tables

[AT @comcastdotnet Tom Wickerath]

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
__________________________________________

"souchie40" wrote:

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

[souchie40]

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

[AT @comcastdotnet Tom Wickerath]

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.

[souchie40]

Tom,
Thank you very much

However I made a big booby and didn't stipulate the version of Access this
was being used in, unfortunately I use Access 97 at work although this will
be used on laptops with later versions the networked PCs only have 97 so I
need this to work in 97, when I tried the code earlier I got an error 3200. I
would be willing to send a copy of the DB if that would help.

The comments about the field names was very useful for any further work
however those names come from a seperate db that I can't change

TIA

Souchie40

"Tom Wickerath" wrote:

> Correction

>
> I wrote:
>
> > You have field names that include spaces, # signs, parentheses ( ),
> > hyphen (-), and $.
>

> You don't have field names with # signs. My mistake. :-)
>
>

[AT @comcastdotnet Tom Wickerath]

Hi Souchie,

The only change that you should need to make for Access 97 is to set the DAO
reference to the "Microsoft DAO 3.51 Object Library" instead of the version
4.0 library. I just tested the code by converting it to an Access 97
database, and it works fine.

I did make one slight improvement to the error handling section. I found
that two different errors were generated, depending on the version of Access,
if the linked table was not present when this line of code was executed:

DoCmd.DeleteObject acTable, "Planned Procurement1"

So, my amended error handler includes a Select Case .... End Select
construct to get around this problem. Here it is:

ProcError:
Select Case Err.Number
'3011 returned in A97 if linked table is not found when
'attempting to delete it. Error 7874 was returned for A2002.
Case 3011, 7874
Resume Next
Case Else

MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Import_Data..."

End Select

Resume ExitProc
Resume
End Function

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

[souchie40]

Hi Tom,

When I run the code and it gets to the 'Delete Planned Procurement' I get an
error message "Error 3200: The record cannot be deleted or changed because
table 'Ship Configuration' includes related information" what the idea is, is
to delete records that have been removed from the source DB ie table 'Planned
procurement1' is there a way around this?

TIA
Souchie

[souchie40]

Hi Tom,

Thanks for all the help that works a treat I had to modify my delete query
to find unmatched and then delete.

I have 1 further question though the message box that pops up at the end
tells me the total number of records that have been updated ie all records
within the table, is there any way that the number that has been modified -
ie records that either weren't deleted or appended- can be counted?

TIA

Souchie

[AT @comcastdotnet Tom Wickerath]

Hi Souchie,

Thanks for posting back. I actually just woke up about 15 minutes ago, and I
was already started on a reply for your 1:26 AM PST post. By replying back,
you saved me the time of an unnecessary response.

I don't understand the last part of your new question. You wrote:

"...is there any way that the number that has been modified - ie records

that either weren't deleted or appended- can be counted?"

Records that weren't deleted or appended would not be considered modified
records, would they? If you meant to ask "...is there any way that the
number that has *not* been modified..." then the answer is certainly yes.
However, it's going to be a lot easier for me if I can work with your actual
database. Is there any way that you can send me a copy, with dummy records if
necessary?

I'm going off-line now to get ready for work, but I'll be sure to follow up
later tonight if I hear back from you.

Tom

QWaos168@XScom cast. Dnet (<--Remove all capitalized letters and spaces).

[souchie40]

Hi Tom,

If you give me a suitable email address I will be happy to send a copy of
the db, what I mine by modified is that records in the linked table can be
changed and I would like to know which ones so I wouldn't have to go
sereaching the whole list to find the odd change.

Souchie

[souchie40]

Hi Tom,

When I try to email to the address you gave I get a undeliverable message
back, despite changing the caps and removing the spaces

Souchie

"Tom Wickerath" wrote:

> Hi Souchie,
>
> Here it is, in a slightly obfuscated form, to make it a bit more difficult
> for the spam bots to harvest my e-mail address.

>
> QWaos168@XScom cast. Dnet (<--Remove all capitalized letters and spaces).

> __________________________________________
>
> "souchie40" wrote:
>
> > Hi Tom,
> >

> > If you give me a suitable email address....

[AT @comcastdotnet Tom Wickerath]

Hi Souchie,

Remove the caps.....don't change them to lowercase.

Tom

[souchie40]

Hi Tom,

Oops, you should get it now if not then please post back and I'll try again