473,327 Members | 1,936 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

filter in copying data from other database

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_database2
SELECT fields_from_table_in_database1 FROM table_in_database1 IN
directory_of_database1. i was trying to put a WHERE command but i
always get an error. please help.

nick_faye
Nov 12 '05 #1
4 1667
iv******@yahoo.com (nick_faye) wrote in message news:<8a**************************@posting.google. com>...
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_database2
SELECT fields_from_table_in_database1 FROM table_in_database1 IN
directory_of_database1. i was trying to put a WHERE command but i
always get an error. please help.

nick_faye


These worked for me using ID as a primary key:

Different names, same structure:

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

Same name, same structure:

INSERT INTO tblData SELECT * FROM tblData IN
'N:\Databases\Database1.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
Nov 12 '05 #2
ja******@oakland.edu (James Fortune) wrote in message news:<a6*************************@posting.google.c om>...
iv******@yahoo.com (nick_faye) wrote in message news:<8a**************************@posting.google. com>...
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_database2
SELECT fields_from_table_in_database1 FROM table_in_database1 IN
directory_of_database1. i was trying to put a WHERE command but i
always get an error. please help.

nick_faye


These worked for me using ID as a primary key:

Different names, same structure:

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

Same name, same structure:

INSERT INTO tblData SELECT * FROM tblData IN
'N:\Databases\Database1.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

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.
Nov 12 '05 #3
iv******@yahoo.com (nick_faye) wrote in message news:<8a**************************@posting.google. com>...
ja******@oakland.edu (James Fortune) wrote in message news:<a6*************************@posting.google.c om>...
iv******@yahoo.com (nick_faye) wrote in message news:<8a**************************@posting.google. com>...
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_database2
SELECT fields_from_table_in_database1 FROM table_in_database1 IN
directory_of_database1. i was trying to put a WHERE command but i
always get an error. please help.

nick_faye


These worked for me using ID as a primary key:

Different names, same structure:

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

Same name, same structure:

INSERT INTO tblData SELECT * FROM tblData IN
'N:\Databases\Database1.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

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.


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
Nov 12 '05 #4
ja******@oakland.edu (James Fortune) wrote in message news:<a6**************************@posting.google. com>...
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


{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 cmdTransferInformation_Click()
'Need to use ADO to connect to SQL Server data
Dim connBackend As ADODB.Connection 'ADODB connection
Dim strConn As String
Dim cmdGo As ADODB.Command 'ADODB.Command
Dim prmName As ADODB.Parameter 'ADODB.Parameter
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 lngOrderDetailIDSSMax As Long
Dim MaxRS As Recordset

Set MyDB = CurrentDb

Set connBackend = New ADODB.Connection

strConn = "Driver={SQL Server}; Network Library=DBMSSOCN; Data
Source=MySource; Uid=MyUid; Pwd=MyPassword;"
connBackend.Open strConn

'Eventually we'll want to use a stored procedure to return the results
'Set cmdGo = Server.CreateObject("ADODB.Command")
'Set cmdGo.ActiveConnection = connBackend
'cmdGo.CommandText = "SP_GetLevel0ID"
iCmdStoredProc = 4
'cmdGo.CommandType = iCmdStoredProc
'prmName.Value = "Chemical, Fluids and Lubricants"
'prmName.Value = cbxLevel0Pick.Text
'cmdGo.Parameters.Append prmName

lngCountO = 0
lngCountOD = 0

'------------------------------------------ tblOrders
Set tdfTemp = MyDB.TableDefs("tblOrders")
lngFieldCount = tdfTemp.Fields.Count
Set ToRS = MyDB.OpenRecordset("tblOrders", dbOpenDynaset)
'----Find where we left off
Set MaxRS = MyDB.OpenRecordset("SELECT MAX(OrderIDSS) AS MaxID FROM
tblOrders;", dbOpenSnapshot)
lngOrderIDSSMax = 0
If MaxRS.RecordCount > 0 Then
If Not IsNull(MaxRS("MaxID")) Then lngOrderIDSSMax = MaxRS("MaxID")
End If
MaxRS.Close
Set MaxRS = Nothing
'----
Set FromRS = New ADODB.Recordset
Set FromRS.ActiveConnection = connBackend
FromRS.CursorType = 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.Caption = "tblOrders"
DoEvents
strTemp = "SELECT * FROM tblOrders WHERE OrderIDSS > " &
CStr(lngOrderIDSSMax) & ";"
FromRS.Open strTemp, connBackend, , , 1
If Not FromRS.EOF And Not FromRS.BOF Then
FromRS.MoveLast
lngCountO = FromRS.RecordCount
ReDim OID(lngCountO) As Long
FromRS.MoveFirst
dtImportDate = Format(Now(), "mm/dd/yy")
dtImportTime = Format(Now(), "hh:nn ampm")
For lngI = 1 To lngCountO
lblStatus2.Caption = CStr(lngI)
DoEvents
OID(lngI) = FromRS("OrderIDSS")
ToRS.AddNew
'I should use this tabledef technique more often
For lngJ = 0 To lngFieldCount - 1
ToRS(tdfTemp.Fields(lngJ).Name) =
FromRS(tdfTemp.Fields(lngJ).Name)
Next lngJ
ToRS("ImportDate") = dtImportDate
ToRS("ImportTime") = dtImportTime
ToRS.Update
If lngI <> lngCountO Then FromRS.MoveNext
Next lngI
lblStatus2.Caption = "Done."
DoEvents
End If
Set tdfTemp = Nothing
FromRS.Close
Set FromRS = Nothing
ToRS.Close
Set ToRS = Nothing
'------------------------------------------ tblOrderDetails
Set tdfTemp = MyDB.TableDefs("tblOrderDetails")
lngFieldCount = tdfTemp.Fields.Count
Set ToRS = MyDB.OpenRecordset("tblOrderDetails", dbOpenDynaset)
Set FromRS = New ADODB.Recordset
'----Find where we left off
Set MaxRS = MyDB.OpenRecordset("SELECT MAX(OrderDetailIDSS) AS MaxID
FROM tblOrderDetails;", dbOpenSnapshot)
lngOrderDetailIDSSMax = 0
If MaxRS.RecordCount > 0 Then
If Not IsNull(MaxRS("MaxID")) Then lngOrderDetailIDSSMax =
MaxRS("MaxID")
End If
MaxRS.Close
Set MaxRS = Nothing
'----
Set FromRS.ActiveConnection = connBackend
FromRS.CursorType = 1
FromRS.LockType = 3

'Append.
lblStatus1.Caption = "tblOrderDetails"
DoEvents
strTemp = "SELECT * FROM tblOrderDetails WHERE OrderDetailIDSS > " &
CStr(lngOrderDetailIDSSMax) & ";"
FromRS.Open strTemp, connBackend, , , 1
If Not FromRS.EOF And Not FromRS.BOF Then
FromRS.MoveLast
lngCountOD = FromRS.RecordCount
ReDim ODID(lngCountOD) As Long
FromRS.MoveFirst
For lngI = 1 To lngCountOD
lblStatus2.Caption = CStr(lngI)
DoEvents
ODID(lngI) = FromRS("OrderDetailIDSS")
ToRS.AddNew
For lngJ = 0 To lngFieldCount - 1
ToRS(tdfTemp.Fields(lngJ).Name) =
FromRS(tdfTemp.Fields(lngJ).Name)
Next lngJ
ToRS.Update
If lngI <> lngCountOD Then FromRS.MoveNext
Next lngI
lblStatus2.Caption = "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.Caption = "Deleting website orders"
'DoEvents
''Now get the maximum IDSS that was brought down
'Set MaxRS = MyDB.OpenRecordset("SELECT MAX(OrderIDSS) AS MaxID FROM
tblOrders;", dbOpenSnapshot)
'lngOrderIDSSMax = 0
'If MaxRS.RecordCount > 0 Then
' If Not IsNull(MaxRS("MaxID")) Then lngOrderIDSSMax = MaxRS("MaxID")
'End If
'MaxRS.Close
'Set MaxRS = Nothing
''----
'strTemp = "DELETE tblOrders FROM tblOrders WHERE OrderIDSS <= " &
CStr(lngOrderIDSSMax) & ";"
'connBackend.Execute strTemp
'For lngI = 1 To 5000
' DoEvents
'Next lngI
''-------------------------------------- Delete website order details
'lblStatus1.Caption = "Deleting website details"
'DoEvents
''Now get the maximum IDSS that was brought down
'Set MaxRS = MyDB.OpenRecordset("SELECT MAX(OrderDetailIDSS) AS MaxID
FROM tblOrderDetails;", dbOpenSnapshot)
'lngOrderDetailIDSSMax = 0
'If MaxRS.RecordCount > 0 Then
' If Not IsNull(MaxRS("MaxID")) Then lngOrderDetailIDSSMax =
MaxRS("MaxID")
'End If
'MaxRS.Close
'Set MaxRS = Nothing
''----
'strTemp = "DELETE tblOrderDetails FROM tblOrderDetails WHERE
OrderDetailIDSS <= " & CStr(lngOrderDetailIDSSMax) & ";"
'connBackend.Execute strTemp
For lngI = 1 To 5000
DoEvents
Next lngI
lblStatus1.Caption = "Done."
DoEvents
'--------------------------------------
connBackend.Close
Set connBackend = Nothing
Set MyDB = Nothing
End Sub

James A. Fortune
Nov 12 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: Matt | last post by:
Hi everyone, still pretty new to MySQL. I was wondering if there is a way to automatically filter records based on a mysql userlogin name?? I have serveral databases that I want to combine in...
1
by: Robert Neville | last post by:
I would like to add filter functionality to my database whether through the Main form or the subform. This question may be rudimentary, yet I have not less experience with filtering data outside...
0
by: CSDunn | last post by:
Hello, I have a problem with field filtering between an Access 2000 Project form (the application is called CELDT), and the report that shows the results of the filter. Both the form and the...
4
by: nick_faye | last post by:
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...
2
by: Lenin Torres | last post by:
Hi everybody I have an Union Query that works fine. I used this query as the RecordSource for a Form. That Form is used as a subform in another form. Everything works fine, except for the "Filter...
1
by: Sundararajan | last post by:
Dear Folks, Please clarify me on whether a filter on dataview is a performance bottle neck. we know that we cannot apply successive filters to a data view. so the better way is having the 'And'...
11
by: Bob | last post by:
I am in the process of upgrading an Access database to SQL Server (and climbing that learning curve!). The wizard happily upgraded all the tables and I can link to them OK using ODBC. The...
2
by: chiinook | last post by:
I have been creating a contact management database which now has about 300 records in it. I've been filtering using the "filter by form" command but I'd like to automate this with a set of buttons...
1
by: Mindspring | last post by:
I need some help with filtering. I not very savvy (yet) with c# so I am having a hard time figuring out how to filter results. Here is what I am trying to do. I am pulling data from a database...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.