filter in copying data from other database

[nick_faye]

hi guys,

hope somebody can assist me. i have two ms access database. i
have to copy the entries in database1 to my database2. however, i
have to copy entries from database1 that does not exist yet in the
entries in my database2. i am using INSERT INTO table_in_databa se2
SELECT fields_from_tab le_in_database1 FROM table_in_databa se1 IN
directory_of_da tabase1. i was trying to put a WHERE command but i
always get an error. please help.

nick_faye

[James Fortune]

Re: filter in copying data from other database

ivpfmc28@yahoo. com (nick_faye) wrote in message news:<8a5e9892. 0404041833.45d7 4bbf@posting.go ogle.com>...[color=blue]
> hi guys,
>
> hope somebody can assist me. i have two ms access database. i
> have to copy the entries in database1 to my database2. however, i
> have to copy entries from database1 that does not exist yet in the
> entries in my database2. i am using INSERT INTO table_in_databa se2
> SELECT fields_from_tab le_in_database1 FROM table_in_databa se1 IN
> directory_of_da tabase1. i was trying to put a WHERE command but i
> always get an error. please help.
>
> nick_faye[/color]

These worked for me using ID as a primary key:

Different names, same structure:

INSERT INTO tblData1 SELECT * FROM tblData IN
'N:\Databases\D atabase1.mdb' WHERE ((([tblData].[ID])
NOT IN (SELECT ID FROM tblData1)));

Same name, same structure:

INSERT INTO tblData SELECT * FROM tblData IN
'N:\Databases\D atabase1.mdb' WHERE ((([ID]) NOT
IN (SELECT ID FROM tblData)));

Note that for large tables the NOT IN construct can be
very slow so I try to find another way when performance
is an issue.

James A. Fortune

[nick_faye]

Re: filter in copying data from other database

jafortun@oaklan d.edu (James Fortune) wrote in message news:<a6ed3ce7. 0404042241.798e 78d@posting.goo gle.com>...[color=blue]
> ivpfmc28@yahoo. com (nick_faye) wrote in message news:<8a5e9892. 0404041833.45d7 4bbf@posting.go ogle.com>...[color=green]
> > hi guys,
> >
> > hope somebody can assist me. i have two ms access database. i
> > have to copy the entries in database1 to my database2. however, i
> > have to copy entries from database1 that does not exist yet in the
> > entries in my database2. i am using INSERT INTO table_in_databa se2
> > SELECT fields_from_tab le_in_database1 FROM table_in_databa se1 IN
> > directory_of_da tabase1. i was trying to put a WHERE command but i
> > always get an error. please help.
> >
> > nick_faye[/color]
>
> These worked for me using ID as a primary key:
>
> Different names, same structure:
>
> INSERT INTO tblData1 SELECT * FROM tblData IN
> 'N:\Databases\D atabase1.mdb' WHERE ((([tblData].[ID])
> NOT IN (SELECT ID FROM tblData1)));
>
> Same name, same structure:
>
> INSERT INTO tblData SELECT * FROM tblData IN
> 'N:\Databases\D atabase1.mdb' WHERE ((([ID]) NOT
> IN (SELECT ID FROM tblData)));
>
> Note that for large tables the NOT IN construct can be
> very slow so I try to find another way when performance
> is an issue.
>
> James A. Fortune[/color]


I tried what you had suggested and it worked. Thanks. Luckily, my
tables that required the NOT IN contruct were not large ones. Can you
share any workaround how to do this in case I have large tables? I'm
sure I will be in this situation in the near future. Thanks in
advance.

[James Fortune]

Re: filter in copying data from other database

ivpfmc28@yahoo. com (nick_faye) wrote in message news:<8a5e9892. 0404112238.25d0 103c@posting.go ogle.com>...[color=blue]
> jafortun@oaklan d.edu (James Fortune) wrote in message news:<a6ed3ce7. 0404042241.798e 78d@posting.goo gle.com>...[color=green]
> > ivpfmc28@yahoo. com (nick_faye) wrote in message news:<8a5e9892. 0404041833.45d7 4bbf@posting.go ogle.com>...[color=darkred]
> > > hi guys,
> > >
> > > hope somebody can assist me. i have two ms access database. i
> > > have to copy the entries in database1 to my database2. however, i
> > > have to copy entries from database1 that does not exist yet in the
> > > entries in my database2. i am using INSERT INTO table_in_databa se2
> > > SELECT fields_from_tab le_in_database1 FROM table_in_databa se1 IN
> > > directory_of_da tabase1. i was trying to put a WHERE command but i
> > > always get an error. please help.
> > >
> > > nick_faye[/color]
> >
> > These worked for me using ID as a primary key:
> >
> > Different names, same structure:
> >
> > INSERT INTO tblData1 SELECT * FROM tblData IN
> > 'N:\Databases\D atabase1.mdb' WHERE ((([tblData].[ID])
> > NOT IN (SELECT ID FROM tblData1)));
> >
> > Same name, same structure:
> >
> > INSERT INTO tblData SELECT * FROM tblData IN
> > 'N:\Databases\D atabase1.mdb' WHERE ((([ID]) NOT
> > IN (SELECT ID FROM tblData)));
> >
> > Note that for large tables the NOT IN construct can be
> > very slow so I try to find another way when performance
> > is an issue.
> >
> > James A. Fortune[/color]
>
>
> I tried what you had suggested and it worked. Thanks. Luckily, my
> tables that required the NOT IN contruct were not large ones. Can you
> share any workaround how to do this in case I have large tables? I'm
> sure I will be in this situation in the near future. Thanks in
> advance.[/color]

Create a link to tblData in Database1.mdb (assuming Access will append
a 1), then use:
INSERT INTO tblData SELECT tblData1.* FROM tblData1 WHERE tblData1.ID
IN (SELECT tblData1.ID FROM tblData1 LEFT JOIN tblData ON tblData1.ID
= tblData.ID WHERE (((tblData.ID) IS NULL)));

A brute force method is to 'maketable query' the ID's from
Database1.mdb into tblNewIDs and then 'delete query' the ID's that are
in your tblData from tblNewID. Next 'append query' WHERE ID's are in
tblNewID. Then delete tblNewID.

I have not discovered an elegant way to do this with one SQL statement
without a link or temporary table yet partly because the 'ON' part of
the JOIN statement gets lost. I did discover that UNION queries can
use IN 'databasepath' for each part. Also, Access didn't like IN
(SELECT ... UNION SELECT ...).

I had a similar situation come up with an ecommerce app. The Access
adp had to download new orders from a SQL Server database. Once
Access had the new orders it deleted those orders on the server. As
an extra precaution, I had Access check itself for potential duplicate
OrderID's before adding the new ones.

James A. Fortune

[James Fortune]

Re: filter in copying data from other database

jafortun@oaklan d.edu (James Fortune) wrote in message news:<a6ed3ce7. 0404121725.46c8 f81d@posting.go ogle.com>...
[color=blue]
> I had a similar situation come up with an ecommerce app. The Access
> adp had to download new orders from a SQL Server database. Once
> Access had the new orders it deleted those orders on the server. As
> an extra precaution, I had Access check itself for potential duplicate
> OrderID's before adding the new ones.
>
> James A. Fortune[/color]

{long)

I found the code I used. I didn't have time to pretty it up so don't
flame me if it doesn't all work. Also, I wrote some code that
automated putting the order information into HTML templates which were
then supposed to be emailed as attachments to confirm the orders.
What I did was sprinkle "[[FieldName]]" values at appropriate
locations in the HTML template and had Access read the template line
by line. It was complicated by the fact that information from both
tblOrders and tblOrderDetails had to be merged in so I had to
interrupt the tblOrders merge and append lines from an OrderDetails
template then go back to merging from the Orders template. If anyone
is interested I'll post the code I used. It's longer than the code
below so I'll try not to waste bandwidth unless someone requests that
I post it. The user could click a "magic button" on an Access form
and have all the new orders downloaded, deleted from the server, have
email confirmations sent (including having Access watch for folder
inactivity) to the purchaser, have the order information emailed to
the supplier and have a file created that can be easily imported into
QuickBooks. There were even more features, but you get the idea.

'------------
Private Sub cmdTransferInfo rmation_Click()
'Need to use ADO to connect to SQL Server data
Dim connBackend As ADODB.Connectio n 'ADODB connection
Dim strConn As String
Dim cmdGo As ADODB.Command 'ADODB.Command
Dim prmName As ADODB.Parameter 'ADODB.Paramete r
Dim FromRS As ADODB.Recordset 'ADO Recordset
Dim DeleteRS As ADODB.Recordset
Dim ToRS As Recordset 'Recordset
Dim strID As String
Dim strTemp As String
Dim lngI As Long
Dim lngJ As Long
Dim sCatID As String
Dim iMax As Integer
Dim sSKU As String
Dim iCmdStoredProc As Integer
Dim lngCountO As Long
Dim lngCountOD As Long
Dim OID() As Long
Dim ODID() As Long
Dim MyDB As Database
Dim theField As Field
Dim tdfTemp As TableDef
Dim lngFieldCount As Long
Dim dtImportDate As Date
Dim dtImportTime As Date
Dim strSQL As String
Dim lngOrderIDSSMax As Long
Dim lngOrderDetailI DSSMax As Long
Dim MaxRS As Recordset

Set MyDB = CurrentDb

Set connBackend = New ADODB.Connectio n

strConn = "Driver={SQ L Server}; Network Library=DBMSSOC N; Data
Source=MySource ; Uid=MyUid; Pwd=MyPassword; "
connBackend.Ope n strConn

'Eventually we'll want to use a stored procedure to return the results
'Set cmdGo = Server.CreateOb ject("ADODB.Com mand")
'Set cmdGo.ActiveCon nection = connBackend
'cmdGo.CommandT ext = "SP_GetLevel0ID "
iCmdStoredProc = 4
'cmdGo.CommandT ype = iCmdStoredProc
'prmName.Value = "Chemical, Fluids and Lubricants"
'prmName.Value = cbxLevel0Pick.T ext
'cmdGo.Paramete rs.Append prmName

lngCountO = 0
lngCountOD = 0

'------------------------------------------ tblOrders
Set tdfTemp = MyDB.TableDefs( "tblOrders" )
lngFieldCount = tdfTemp.Fields. Count
Set ToRS = MyDB.OpenRecord set("tblOrders" , dbOpenDynaset)
'----Find where we left off
Set MaxRS = MyDB.OpenRecord set("SELECT MAX(OrderIDSS) AS MaxID FROM
tblOrders;", dbOpenSnapshot)
lngOrderIDSSMax = 0
If MaxRS.RecordCou nt > 0 Then
If Not IsNull(MaxRS("M axID")) Then lngOrderIDSSMax = MaxRS("MaxID")
End If
MaxRS.Close
Set MaxRS = Nothing
'----
Set FromRS = New ADODB.Recordset
Set FromRS.ActiveCo nnection = connBackend
FromRS.CursorTy pe = 1
FromRS.LockType = 3
'As each record is copied to the hard drive, put the SQL Server
'ID into an array so that the appropriate records can be
'deleted on the server

'Append. Be sure to fill importdate and importtime
lblStatus1.Capt ion = "tblOrders"
DoEvents
strTemp = "SELECT * FROM tblOrders WHERE OrderIDSS > " &
CStr(lngOrderID SSMax) & ";"
FromRS.Open strTemp, connBackend, , , 1
If Not FromRS.EOF And Not FromRS.BOF Then
FromRS.MoveLast
lngCountO = FromRS.RecordCo unt
ReDim OID(lngCountO) As Long
FromRS.MoveFirs t
dtImportDate = Format(Now(), "mm/dd/yy")
dtImportTime = Format(Now(), "hh:nn ampm")
For lngI = 1 To lngCountO
lblStatus2.Capt ion = CStr(lngI)
DoEvents
OID(lngI) = FromRS("OrderID SS")
ToRS.AddNew
'I should use this tabledef technique more often
For lngJ = 0 To lngFieldCount - 1
ToRS(tdfTemp.Fi elds(lngJ).Name ) =
FromRS(tdfTemp. Fields(lngJ).Na me)
Next lngJ
ToRS("ImportDat e") = dtImportDate
ToRS("ImportTim e") = dtImportTime
ToRS.Update
If lngI <> lngCountO Then FromRS.MoveNext
Next lngI
lblStatus2.Capt ion = "Done."
DoEvents
End If
Set tdfTemp = Nothing
FromRS.Close
Set FromRS = Nothing
ToRS.Close
Set ToRS = Nothing
'------------------------------------------ tblOrderDetails
Set tdfTemp = MyDB.TableDefs( "tblOrderDetail s")
lngFieldCount = tdfTemp.Fields. Count
Set ToRS = MyDB.OpenRecord set("tblOrderDe tails", dbOpenDynaset)
Set FromRS = New ADODB.Recordset
'----Find where we left off
Set MaxRS = MyDB.OpenRecord set("SELECT MAX(OrderDetail IDSS) AS MaxID
FROM tblOrderDetails ;", dbOpenSnapshot)
lngOrderDetailI DSSMax = 0
If MaxRS.RecordCou nt > 0 Then
If Not IsNull(MaxRS("M axID")) Then lngOrderDetailI DSSMax =
MaxRS("MaxID")
End If
MaxRS.Close
Set MaxRS = Nothing
'----
Set FromRS.ActiveCo nnection = connBackend
FromRS.CursorTy pe = 1
FromRS.LockType = 3

'Append.
lblStatus1.Capt ion = "tblOrderDetail s"
DoEvents
strTemp = "SELECT * FROM tblOrderDetails WHERE OrderDetailIDSS > " &
CStr(lngOrderDe tailIDSSMax) & ";"
FromRS.Open strTemp, connBackend, , , 1
If Not FromRS.EOF And Not FromRS.BOF Then
FromRS.MoveLast
lngCountOD = FromRS.RecordCo unt
ReDim ODID(lngCountOD ) As Long
FromRS.MoveFirs t
For lngI = 1 To lngCountOD
lblStatus2.Capt ion = CStr(lngI)
DoEvents
ODID(lngI) = FromRS("OrderDe tailIDSS")
ToRS.AddNew
For lngJ = 0 To lngFieldCount - 1
ToRS(tdfTemp.Fi elds(lngJ).Name ) =
FromRS(tdfTemp. Fields(lngJ).Na me)
Next lngJ
ToRS.Update
If lngI <> lngCountOD Then FromRS.MoveNext
Next lngI
lblStatus2.Capt ion = "Done."
DoEvents
End If
'Set cmdGo = Nothing
Set tdfTemp = Nothing
FromRS.Close
Set FromRS = Nothing
ToRS.Close
Set ToRS = Nothing
''-------------------------------------- Delete website orders
''Only delete orders that have been brought down
'lblStatus1.Cap tion = "Deleting website orders"
'DoEvents
''Now get the maximum IDSS that was brought down
'Set MaxRS = MyDB.OpenRecord set("SELECT MAX(OrderIDSS) AS MaxID FROM
tblOrders;", dbOpenSnapshot)
'lngOrderIDSSMa x = 0
'If MaxRS.RecordCou nt > 0 Then
' If Not IsNull(MaxRS("M axID")) Then lngOrderIDSSMax = MaxRS("MaxID")
'End If
'MaxRS.Close
'Set MaxRS = Nothing
''----
'strTemp = "DELETE tblOrders FROM tblOrders WHERE OrderIDSS <= " &
CStr(lngOrderID SSMax) & ";"
'connBackend.Ex ecute strTemp
'For lngI = 1 To 5000
' DoEvents
'Next lngI
''-------------------------------------- Delete website order details
'lblStatus1.Cap tion = "Deleting website details"
'DoEvents
''Now get the maximum IDSS that was brought down
'Set MaxRS = MyDB.OpenRecord set("SELECT MAX(OrderDetail IDSS) AS MaxID
FROM tblOrderDetails ;", dbOpenSnapshot)
'lngOrderDetail IDSSMax = 0
'If MaxRS.RecordCou nt > 0 Then
' If Not IsNull(MaxRS("M axID")) Then lngOrderDetailI DSSMax =
MaxRS("MaxID")
'End If
'MaxRS.Close
'Set MaxRS = Nothing
''----
'strTemp = "DELETE tblOrderDetails FROM tblOrderDetails WHERE
OrderDetailIDSS <= " & CStr(lngOrderDe tailIDSSMax) & ";"
'connBackend.Ex ecute strTemp
For lngI = 1 To 5000
DoEvents
Next lngI
lblStatus1.Capt ion = "Done."
DoEvents
'--------------------------------------
connBackend.Clo se
Set connBackend = Nothing
Set MyDB = Nothing
End Sub

James A. Fortune