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

Database comparing

P: 6
Hi everyone!

I have some questions, maybe someone can help me...
I write script for table comparing, but it works wery slovly. There is the script, can anyone give some tip how can I make this process faster and if it is possible in VB.
The idea is to compare two tables and if there is some distinction, then is written in third table. Some tables contains more than 200 000 rows and each row contain approximatelly 30 parameeters (columns).
************************************************** *******************************************
Option Compare Database

Sub Parametru_Izmainas()
Dim bssSiemens As Database
Dim rstTableNew As Recordset, rstTableOld As Recordset, rstParamChange As Recordset
Dim rstKonf As Recordset
Dim n As Long, tx As Integer, a As Integer
Dim fname1 As String, fname2 As String, fname3 As String, fname4 As String
Dim TableNew As Variant, TableOld As String, TableList As Variant
Dim strCriteria As String, strCriteriaSect As String
Dim NewParam As Variant, OldParam As Variant
Set bssSiemens = CurrentDb
Set rstParamChange = bssSiemens.OpenRecordset("Izmainas", dbOpenDynaset)
Set rstKonf = bssSiemens.OpenRecordset("Konfiguracija", dbOpenDynaset)

For a = 2 To 5
'If a = 1 Then
' TableList = Array("Bsc")
ElseIf a = 2 Then
TableList = Array("Btsm")
ElseIf a = 3 Then
TableList = Array("Bts")
ElseIf a = 4 Then
TableList = Array("AdjC")
ElseIf a = 5 Then
TableList = Array("Chan")
End If

For Each TableNew In TableList
TableOld = TableNew & "Old"
Set rstTableNew = bssSiemens.OpenRecordset(TableNew)
Set rstTableOld = bssSiemens.OpenRecordset(TableOld, dbOpenDynaset)
If rstTableNew.RecordCount = 0 Then GoTo 300
rstTableNew.MoveFirst
fname1 = rstTableNew.Fields(0).Name
fname2 = rstTableNew.Fields(1).Name
fname3 = rstTableNew.Fields(2).Name
fname4 = rstTableNew.Fields(3).Name
fname5 = rstTableNew.Fields(4).Name
Do While rstTableNew.EOF = False
NewParam = rstTableNew.GetRows(1)
id1 = NewParam(0, 0)
id2 = NewParam(1, 0)
id3 = NewParam(2, 0)
id4 = NewParam(3, 0)
id5 = NewParam(4, 0)

If a = 2 Then
strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" & id2
ElseIf a = 2 Then
strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" & id2 & " AND [" & fname3 & "]=" & id3
ElseIf a = 4 Then
strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" & id2 & " AND [" & fname3 & "]=" & id3 & " AND [" & fname4 & "]=" & id4
ElseIf a = 4 Then
strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" & id2 & " AND [" & fname3 & "]=" & id3 & " AND [" & fname4 & "]=" & id4 & " AND [" & fname5 & "]=" & id5
Else
strCriteria = "[" & fname1 & "]=" & id1

End If
strCriteriaSect = "[bsc]=" & id1 & " AND [btsm]=" & id2 & " AND [bts]=" & id3
rstKonf.FindFirst strCriteriaSect
rstTableOld.FindFirst strCriteria

If rstTableOld.NoMatch Then
rstParamChange.AddNew
rstParamChange!Date = Date - 1
rstParamChange!id1 = id1
rstParamChange!id2 = id2
If a = 3 Then rstParamChange!id3 = id3
If a = 4 Then rstParamChange!id3 = id3
If a = 4 Then rstParamChange!id4 = id4
If a = 5 Then rstParamChange!id3 = id3
If a = 5 Then rstParamChange!id4 = id4
If a = 5 Then rstParamChange!id5 = id5
rstParamChange!Table = TableNew
rstParamChange.Update
rstParamChange.Requery
GoTo 100
Else
OldParam = rstTableOld.GetRows(1)
For n = 0 To rstTableNew.Fields.Count - 1
If NewParam(n, 0) = OldParam(n, 0) Then
tx = 1

Else
If IsNull(NewParam(n, 0)) And IsNull(OldParam(n, 0)) Then GoTo 200
rstParamChange.AddNew
rstParamChange!SectorName = rstKonf.Fields("SectorName")
rstParamChange!Date = Date - 1
rstParamChange!id1 = NewParam(0, 0)
rstParamChange!id2 = NewParam(1, 0)
rstParamChange!id3 = NewParam(2, 0)
rstParamChange!id4 = NewParam(3, 0)
rstParamChange!id5 = NewParam(4, 0)
rstParamChange!Table = TableNew
rstParamChange!Parameter = rstTableNew.Fields(n).Name
rstParamChange!new = NewParam(n, 0)
rstParamChange!old = OldParam(n, 0)
rstParamChange.Update
rstParamChange.Requery
End If

200
Next n
End If

100
Loop

300
Next
Next a
End Sub
Jan 16 '07 #1
Share this Question
Share on Google+
21 Replies


Expert 5K+
P: 8,434
I apologise in advance, as I haven't read through your code in detail yet. But I would like to get a couple of questions answered first.
  • Can you post the table definition? I think we'll need to know about indexes, especially.
  • Are you trying to find whether entire records are identical, or are you interested in identifying records which have the same key field(s) but are different in other fields?
  • Have you tried using the Wizard supplied with Access, to build a "find duplicates query"?
I'm going to re-post your code with tags around it for readability. I've underlined some of the If/ElseIf conditions in the code which don't seem to make sense. Can you check them please?
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Sub Parametru_Izmainas()
  4. Dim bssSiemens As Database
  5. Dim rstTableNew As Recordset, rstTableOld As Recordset, rstParamChange As Recordset
  6. Dim rstKonf As Recordset
  7. Dim n As Long, tx As Integer, a As Integer
  8. Dim fname1 As String, fname2 As String, fname3 As String, fname4 As String
  9. Dim TableNew As Variant, TableOld As String, TableList As Variant
  10. Dim strCriteria As String, strCriteriaSect As String
  11. Dim NewParam As Variant, OldParam As Variant
  12. Set bssSiemens = CurrentDb
  13. Set rstParamChange = bssSiemens.OpenRecordset("Izmainas", dbOpenDynaset)
  14. Set rstKonf = bssSiemens.OpenRecordset("Konfiguracija", dbOpenDynaset)
  15.  
  16. For a = 2 To 5
  17. 'If a = 1 Then
  18. '        TableList = Array("Bsc")
  19.     ElseIf a = 2 Then
  20.         TableList = Array("Btsm")
  21.     ElseIf a = 3 Then
  22.         TableList = Array("Bts")
  23.     ElseIf a = 4 Then
  24.         TableList = Array("AdjC")
  25.     ElseIf a = 5 Then
  26.         TableList = Array("Chan")
  27. End If
  28.  
  29. For Each TableNew In TableList
  30. TableOld = TableNew & "Old"
  31. Set rstTableNew = bssSiemens.OpenRecordset(TableNew)
  32. Set rstTableOld = bssSiemens.OpenRecordset(TableOld, dbOpenDynaset)
  33. If rstTableNew.RecordCount = 0 Then GoTo 300
  34. rstTableNew.MoveFirst
  35. fname1 = rstTableNew.Fields(0).Name
  36. fname2 = rstTableNew.Fields(1).Name
  37. fname3 = rstTableNew.Fields(2).Name
  38. fname4 = rstTableNew.Fields(3).Name
  39. fname5 = rstTableNew.Fields(4).Name
  40. Do While rstTableNew.EOF = False
  41.     NewParam = rstTableNew.GetRows(1)
  42.     id1 = NewParam(0, 0)
  43.     id2 = NewParam(1, 0)
  44.     id3 = NewParam(2, 0)
  45.     id4 = NewParam(3, 0)
  46.     id5 = NewParam(4, 0)
  47.  
  48.     If a = 2 Then
  49.         strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" _
  50.         & id2
  51.     ElseIf a = 2 Then
  52.         strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" _
  53.         & id2 & " AND [" & fname3 & "]=" & id3
  54.     ElseIf a = 4 Then
  55.         strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" _
  56.         & id2 & " AND [" & fname3 & "]=" & id3 & " AND [" & fname4 & "]=" & id4
  57.     ElseIf a = 4 Then
  58.         strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" _
  59.         & id2 & " AND [" & fname3 & "]=" & id3 & " AND [" & fname4 & "]=" _
  60.         & id4 & " AND [" & fname5 & "]=" & id5
  61.     Else
  62.         strCriteria = "[" & fname1 & "]=" & id1
  63.  
  64.     End If
  65.     strCriteriaSect = "[bsc]=" & id1 & " AND [btsm]=" & id2 & " AND [bts]=" & id3
  66.     rstKonf.FindFirst strCriteriaSect
  67.     rstTableOld.FindFirst strCriteria
  68.  
  69.     If rstTableOld.NoMatch Then
  70.           rstParamChange.AddNew
  71.       rstParamChange!Date = Date - 1
  72.       rstParamChange!id1 = id1
  73.       rstParamChange!id2 = id2
  74.       If a = 3 Then rstParamChange!id3 = id3
  75.       If a = 4 Then rstParamChange!id3 = id3
  76.       If a = 4 Then rstParamChange!id4 = id4
  77.       If a = 5 Then rstParamChange!id3 = id3
  78.       If a = 5 Then rstParamChange!id4 = id4
  79.       If a = 5 Then rstParamChange!id5 = id5
  80.       rstParamChange!Table = TableNew
  81.       rstParamChange.Update
  82.       rstParamChange.Requery
  83.             GoTo 100
  84.     Else
  85.         OldParam = rstTableOld.GetRows(1)
  86.         For n = 0 To rstTableNew.Fields.Count - 1
  87.             If NewParam(n, 0) = OldParam(n, 0) Then
  88.                 tx = 1
  89.  
  90.               Else
  91.                 If IsNull(NewParam(n, 0)) And IsNull(OldParam(n, 0)) Then GoTo 200
  92.                 rstParamChange.AddNew
  93.                 rstParamChange!SectorName = rstKonf.Fields("SectorName")
  94.                 rstParamChange!Date = Date - 1
  95.                 rstParamChange!id1 = NewParam(0, 0)
  96.                 rstParamChange!id2 = NewParam(1, 0)
  97.                 rstParamChange!id3 = NewParam(2, 0)
  98.                 rstParamChange!id4 = NewParam(3, 0)
  99.                 rstParamChange!id5 = NewParam(4, 0)
  100.                 rstParamChange!Table = TableNew
  101.                 rstParamChange!Parameter = rstTableNew.Fields(n).Name
  102.                 rstParamChange!new = NewParam(n, 0)
  103.                 rstParamChange!old = OldParam(n, 0)
  104.                 rstParamChange.Update
  105.                 rstParamChange.Requery
  106.             End If
  107.  
  108. 200
  109.         Next n
  110.     End If
  111.  
  112. 100
  113. Loop
  114.  
  115. 300
  116. Next
  117. Next a
  118. End Sub
Jan 16 '07 #2

P: 6
I apologise in advance, as I haven't read through your code in detail yet. But I would like to get a couple of questions answered first.
  • Can you post the table definition? I think we'll need to know about indexes, especially.
  • Are you trying to find whether entire records are identical, or are you interested in identifying records which have the same key field(s) but are different in other fields?
  • Have you tried using the Wizard supplied with Access, to build a "find duplicates query"?
I'm going to re-post your code with tags around it for readability. I've underlined some of the If/ElseIf conditions in the code which don't seem to make sense. Can you check them please?
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Sub Parametru_Izmainas()
  4. Dim bssSiemens As Database
  5. Dim rstTableNew As Recordset, rstTableOld As Recordset, rstParamChange As Recordset
  6. Dim rstKonf As Recordset
  7. Dim n As Long, tx As Integer, a As Integer
  8. Dim fname1 As String, fname2 As String, fname3 As String, fname4 As String
  9. Dim TableNew As Variant, TableOld As String, TableList As Variant
  10. Dim strCriteria As String, strCriteriaSect As String
  11. Dim NewParam As Variant, OldParam As Variant
  12. Set bssSiemens = CurrentDb
  13. Set rstParamChange = bssSiemens.OpenRecordset("Izmainas", dbOpenDynaset)
  14. Set rstKonf = bssSiemens.OpenRecordset("Konfiguracija", dbOpenDynaset)
  15.  
  16. For a = 2 To 5
  17. 'If a = 1 Then
  18. '        TableList = Array("Bsc")
  19.     ElseIf a = 2 Then
  20.         TableList = Array("Btsm")
  21.     ElseIf a = 3 Then
  22.         TableList = Array("Bts")
  23.     ElseIf a = 4 Then
  24.         TableList = Array("AdjC")
  25.     ElseIf a = 5 Then
  26.         TableList = Array("Chan")
  27. End If
  28.  
  29. For Each TableNew In TableList
  30. TableOld = TableNew & "Old"
  31. Set rstTableNew = bssSiemens.OpenRecordset(TableNew)
  32. Set rstTableOld = bssSiemens.OpenRecordset(TableOld, dbOpenDynaset)
  33. If rstTableNew.RecordCount = 0 Then GoTo 300
  34. rstTableNew.MoveFirst
  35. fname1 = rstTableNew.Fields(0).Name
  36. fname2 = rstTableNew.Fields(1).Name
  37. fname3 = rstTableNew.Fields(2).Name
  38. fname4 = rstTableNew.Fields(3).Name
  39. fname5 = rstTableNew.Fields(4).Name
  40. Do While rstTableNew.EOF = False
  41.     NewParam = rstTableNew.GetRows(1)
  42.     id1 = NewParam(0, 0)
  43.     id2 = NewParam(1, 0)
  44.     id3 = NewParam(2, 0)
  45.     id4 = NewParam(3, 0)
  46.     id5 = NewParam(4, 0)
  47.  
  48.     If a = 2 Then
  49.         strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" _
  50.         & id2
  51.     ElseIf a = 2 Then
  52.         strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" _
  53.         & id2 & " AND [" & fname3 & "]=" & id3
  54.     ElseIf a = 4 Then
  55.         strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" _
  56.         & id2 & " AND [" & fname3 & "]=" & id3 & " AND [" & fname4 & "]=" & id4
  57.     ElseIf a = 4 Then
  58.         strCriteria = "[" & fname1 & "]=" & id1 & " AND [" & fname2 & "]=" _
  59.         & id2 & " AND [" & fname3 & "]=" & id3 & " AND [" & fname4 & "]=" _
  60.         & id4 & " AND [" & fname5 & "]=" & id5
  61.     Else
  62.         strCriteria = "[" & fname1 & "]=" & id1
  63.  
  64.     End If
  65.     strCriteriaSect = "[bsc]=" & id1 & " AND [btsm]=" & id2 & " AND [bts]=" & id3
  66.     rstKonf.FindFirst strCriteriaSect
  67.     rstTableOld.FindFirst strCriteria
  68.  
  69.     If rstTableOld.NoMatch Then
  70.           rstParamChange.AddNew
  71.       rstParamChange!Date = Date - 1
  72.       rstParamChange!id1 = id1
  73.       rstParamChange!id2 = id2
  74.       If a = 3 Then rstParamChange!id3 = id3
  75.       If a = 4 Then rstParamChange!id3 = id3
  76.       If a = 4 Then rstParamChange!id4 = id4
  77.       If a = 5 Then rstParamChange!id3 = id3
  78.       If a = 5 Then rstParamChange!id4 = id4
  79.       If a = 5 Then rstParamChange!id5 = id5
  80.       rstParamChange!Table = TableNew
  81.       rstParamChange.Update
  82.       rstParamChange.Requery
  83.             GoTo 100
  84.     Else
  85.         OldParam = rstTableOld.GetRows(1)
  86.         For n = 0 To rstTableNew.Fields.Count - 1
  87.             If NewParam(n, 0) = OldParam(n, 0) Then
  88.                 tx = 1
  89.  
  90.               Else
  91.                 If IsNull(NewParam(n, 0)) And IsNull(OldParam(n, 0)) Then GoTo 200
  92.                 rstParamChange.AddNew
  93.                 rstParamChange!SectorName = rstKonf.Fields("SectorName")
  94.                 rstParamChange!Date = Date - 1
  95.                 rstParamChange!id1 = NewParam(0, 0)
  96.                 rstParamChange!id2 = NewParam(1, 0)
  97.                 rstParamChange!id3 = NewParam(2, 0)
  98.                 rstParamChange!id4 = NewParam(3, 0)
  99.                 rstParamChange!id5 = NewParam(4, 0)
  100.                 rstParamChange!Table = TableNew
  101.                 rstParamChange!Parameter = rstTableNew.Fields(n).Name
  102.                 rstParamChange!new = NewParam(n, 0)
  103.                 rstParamChange!old = OldParam(n, 0)
  104.                 rstParamChange.Update
  105.                 rstParamChange.Requery
  106.             End If
  107.  
  108. 200
  109.         Next n
  110.     End If
  111.  
  112. 100
  113. Loop
  114.  
  115. 300
  116. Next
  117. Next a
  118. End Sub
OK...
I have two tables, in bouth tables are some fields with identificators and other fields with some parameters. Identificators can not been changed they come from other table. One of table are updated every morning (new) other table contain information abouth yesterday (old). I'm interesting to find out if in "new" table some of parameters are changed, maybe there is some new records or maybe some records are deleted. All this infomation I try to put in third table.
The parameters moustly are numbers.

Abouth If/Elseif object... yes these are wrong. Correct is - first elseif "a = 3" and last is "a = 5".
Jan 17 '07 #3

P: 6
Maybe I need to find another script to do this...?!
Jan 17 '07 #4

Expert 5K+
P: 8,434
Maybe I need to find another script to do this...?!
Sorry, been very busy. It may be a few hours yet before I have a chance to look into this properly. But, I think you should be able to do three fairly simple SQL queries to find new, removed and modified records.

I'm not expert in SQL. I'll check with an expert or two from the Access forum, and one or more of us will get back to you.
Jan 17 '07 #5

NeoPa
Expert Mod 15k+
P: 31,494
I can't commit any time to this tonight as it's too late already for me.
I thought I'd register the thread though anyway to remind me to look tomorrow.
When I look again tomorrow it would be nice to find clear answers to Killer's original questions posted in here.
Jan 18 '07 #6

P: 6
Ifsomeone give e-mail adress, then I can send screenshots of all three tables... maybe it will be easy... it's hard to tell how the tables are look like.

Yes, I try to use these access wizards, but it didn't help... :(
Jan 18 '07 #7

NeoPa
Expert Mod 15k+
P: 31,494
Ifsomeone give e-mail adress, then I can send screenshots of all three tables... maybe it will be easy... it's hard to tell how the tables are look like.

Yes, I try to use these access wizards, but it didn't help... :(
No. Sending stuff via e-mail defeats the purpose of these forums.
Please post the information in a form similar to this :
Posting Table/Dataset MetaData
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblStudent
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. MaxMark; Numeric
  7. MinMark; Numeric
Jan 18 '07 #8

Expert 5K+
P: 8,434
When I had a go at explaining your situation to mmccarthy (let's hope I got it right), here was her response (edited slightly)...
The easiest way to do this is with three recordsets. This pseudo code is DAO based, you'll have to translate it to ADO if necessary.

rs1 - Set to TableNew (todays records)
rs2 - Set to TableOld (yesterdays records)
rs3 - Set to transaction table to record differences

Expand|Select|Wrap|Line Numbers
  1. Dim recordChanged As Boolean 
  2.  
  3. rs.MoveFirst
  4. Do Until rs1.EOF
  5.   recordChanged = False
  6.   rs2.FindFirst "[Unique_ID_Field]=" & rs1![Unique_ID_Field] 
  7.   If rs2!Field1 <> rs1.Field1 Then
  8.     recordChanged = True
  9.   ElseIf rs2!Field2 <> rs1.Field2 Then
  10.     recordChanged = True
  11.   Else .....
  12.   End If
  13.  
  14.   If recordChanged = True Then
  15.     rs3.AddNew
  16.     ..
  17.     ..
  18.     ..
  19.     rs3.Update
  20.   End If
  21.   rs1.MoveNext
  22. Loop
Jan 18 '07 #9

Expert 5K+
P: 8,434
I'm still of the opinion that you should not need to do the work in your code. It should be enough to execute a simple(?) SQL string for each of the three operations.

I just used the Find Unmatched Query Wizard to produce this query, which lists all the records from "yesterday" which have been deleted "today". It appear to work OK. Wouldn't you say this could be easily adapted to append these records to a "differences" table rather than simply displaying them?
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM tbYesterday
  2.   LEFT JOIN tbToday ON tbYesterday.KeyField = tbToday.KeyField
  3.   WHERE (((tbToday.KeyField) Is Null));
Finding records which were added should be a simple matter of reversing this "unmatched query" to find records today which didn't exist yesterday. I'll attempt it here...
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM tbToday
  2.   LEFT JOIN tbYesterday ON tbToday.KeyField = tbYesterday.KeyField
  3.   WHERE (((tbYesterday.KeyField) Is Null));
As for the modified records, hm... I'll get back to you.
Jan 18 '07 #10

Expert 5K+
P: 8,434
Ok, I built a query which returns all records from today's file which existed yesterday but have been modified in any way. It's not pretty (and I hope I didn't stuff it up while changing field names to protect the innocent) but here goes...
Expand|Select|Wrap|Line Numbers
  1. SELECT tbToday.*, "U" As [Action] FROM tbToday
  2.   INNER JOIN tbYesterday ON tbToday.JobNum = tbYesterday.JobNum
  3.   WHERE ((([tbToday].[Field1] & [tbToday].[Field2] & [tbToday].[Field3])
  4.   <>([tbYesterday].[Field1] & [tbYesterday].[Field2] & [tbYesterday].[Field3])));
Once again, this is just a SELECT query, but it shouldn't be too hard to adapt it to append the modified records to another table. (Note that just for fun, I've included an "action" field with a constant value of "U" for Update, so if this was being appended to an actions table you would be able to tell why. Something similar could be done to flag Added and Deleted records.)

Since this is actually fairly simple stuff, I'm guessing there must be a reason why the resident Access experts don't think it's the way to go. Anyone care to elaborate? I realise that while it appears to work, that doesn't automatically make it good design.
Jan 18 '07 #11

NeoPa
Expert Mod 15k+
P: 31,494
Like you Killer I'm of the opinion that SQL would work well for the basic concept. I'm a little less tolerant of members expecting the world when they can't even post replies to questions posted in their thread however. I've held off giving any time to this until at least the OP gets involved.
I didn't like to see you out on a limb though - hence my brief visit.
Jan 18 '07 #12

Expert 5K+
P: 8,434
Like you Killer I'm of the opinion that SQL would work well for the basic concept. I'm a little less tolerant of members expecting the world when they can't even post replies to questions posted in their thread however. I've held off giving any time to this until at least the OP gets involved.
I didn't like to see you out on a limb though - hence my brief visit.
Thanks NeoPa.

We just have to be patient, I guess. Not everyone has the time to hang around TheScripts every day.
Jan 18 '07 #13

NeoPa
Expert Mod 15k+
P: 31,494
That's not a problem Killer - I never expect that. I haven't unsubscribed from the thread or anything - just waiting patiently for a response. If they're not there they won't be missing an answer ;) Also I have plenty of other threads that do need more immediate attention.
Jan 18 '07 #14

P: 6
Good morning ;)

Thanks, I will try these examples today... I hope so...
I newly begin new work, and there I meet thing what I never do before - programming. It is not my prior task of work, but...

Thanks a lot for helping... :)
Jan 19 '07 #15

Expert 5K+
P: 8,434
Thanks a lot for helping... :)
We're always happy to help. Good luck!

NeoPa, how would one go about converting one of my SELECT statements to an append query? It's Friday evening, and I've gone blank.
Jan 19 '07 #16

P: 6
About comparing tables... I canmade some of them queries, but not for all of them... For some tables are more than 40 columns, and I can't put them all in query... In addition there will be very much query's if I will go that way... so...
I have 70 objects and for each minimum 5 tables... As you know, I need tables for yesterday too, that means 10 tables for each object... At the moment, two tables comparing takes 30 min... :D You can calculate yourself how much time i need, to comapre all tables... But I need to do this every morning...
Jan 19 '07 #17

NeoPa
Expert Mod 15k+
P: 31,494
We're always happy to help. Good luck!

NeoPa, how would one go about converting one of my SELECT statements to an append query? It's Friday evening, and I've gone blank.
I've included the main (top) part of the Help article for reference here.
INSERT INTO Statement
Adds a record or multiple records to a table. This is referred to as an append query.

Syntax
Multiple-record append query:

INSERT INTO target [(field1[, field2[, ...]])] [IN externaldatabase]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

Single-record append query:

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

The INSERT INTO statement has these parts:

Part Description
target The name of the table or query to append records to.
field1, field2 Names of the fields to append data to, if following a target argument, or the names of fields to obtain data from, if following a source argument.
externaldatabase The path to an external database. For a description of the path, see the IN clause.
source The name of the table or query to copy records from.
tableexpression The name of the table or tables from which records are inserted. This argument can be a single table name or a compound resulting from an INNER JOIN, LEFT JOIN, or RIGHT JOIN operation or a saved query.
value1, value2 The values to insert into the specific fields of the new record. Each value is inserted into the field that corresponds to the value's position in the list: value1 is inserted into field1 of the new record, value2 into field2, and so on. You must separate values with a comma, and enclose text fields in quotation marks (' ').
You would typically use the first version and add the first line to your existing SELECT SQL.
To find such help in Access search in the Access Help (rather than the VBA Window one) for 'Jet SQL'.
You need the Help system to open up the 'Microsoft Jet SQL Reference' Tree.
Jan 19 '07 #18

NeoPa
Expert Mod 15k+
P: 31,494
About comparing tables... I canmade some of them queries, but not for all of them... For some tables are more than 40 columns, and I can't put them all in query... In addition there will be very much query's if I will go that way... so...
I have 70 objects and for each minimum 5 tables... As you know, I need tables for yesterday too, that means 10 tables for each object... At the moment, two tables comparing takes 30 min... :D You can calculate yourself how much time i need, to comapre all tables... But I need to do this every morning...
Please have a look at Post #2 where killer asks you for some information. You will find you get more help if you reply to all posts in your threads.

MODERATOR.
Jan 19 '07 #19

Expert 5K+
P: 8,434
I've included the main (top) part of the Help article for reference here.
Thanks for that.
... To find such help in Access search in the Access Help (rather than the VBA Window one) for 'Jet SQL'.
You need the Help system to open up the 'Microsoft Jet SQL Reference' Tree.
I have the MSDN library installed (forget what version) and it makes finding things surprisingly difficult. Especially when working with VB, I often think about removing it and just going back to the online help supplied with the product.
Jan 19 '07 #20

Expert 5K+
P: 8,434
...For some tables are more than 40 columns, and I can't put them all in query... In addition there will be very much query's if I will go that way... so... I have 70 objects and for each minimum 5 tables... As you know, I need tables for yesterday too, that means 10 tables for each object...
Keep in mind that these queries can be built in the Access query designer, and saved with a name. Then in VB you can just call the query by name. If they are named well, it may make the process easier to follow.

You can calculate yourself how much time i need, to comapre all tables... But I need to do this every morning...
Oh boy, I know that feeling! I've seen daily processes that ran for 26 hours. :)

Don't worry, I'm sure we can find ways to improve things.

One way I sometimes do this at work is to move the data out of the database and work on sequential files. Diffrent environment, but if necessary I expect we can apply the concept here.
Jan 19 '07 #21

NeoPa
Expert Mod 15k+
P: 31,494
Thanks for that.
I have the MSDN library installed (forget what version) and it makes finding things surprisingly difficult. Especially when working with VB, I often think about removing it and just going back to the online help supplied with the product.
That's why I posted how to find it.
It can be a real bitch finding (especially SQL) things on there.
Jan 19 '07 #22

Post your reply

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