Appending to other database based on status of column (1 Viewer)

[DevAccess]

Hello there,

I have database called X, X database is master database with all records and has Master table and has column called Status which can have value of "New", "Old", "Repair" as first three letters.

Now my query is that I want to create database Y which would hold only those records of X database's "master table" who has column which has "NEW" as first three letters. I want to keep appending these records from X database to Y database after some intervals.

Can anybody please help me on this ?

Thanks
DD

 

[sneuberg]

This web page might help with the going from X to Y part. I suggest using the query designer to get an append query working the way you want in X and then use the information in the web page to extend the query to Y. To get records whose field values start with "New" you will use the LIKE condition in your criteria. Far as having this query run at intervals you can run the query from a form's timer event.

 

[Gasman]

Hello there,

I have database called X, X database is master database with all records and has Master table and has column called Status which can have value of "New", "Old", "Repair" as first three letters.

Now my query is that I want to create database Y which would hold only those records of X database's "master table" who has column which has "NEW" as first three letters. I want to keep appending these records from X database to Y database after some intervals.

Can anybody please help me on this ?

Thanks
DD

PMFJI,
What happens when the first batch of NEW records are appended?, are they going to be appended again?. They will (with current logic) unless, you have some way of determining they have already been processed?

 

[Minty]

Why not link to the master table in the Y database and create a read only query / form to view the New records. Copying data is almost always not the correct way to go.

 

[DevAccess]

Hi Minty,
Can you please help me your statement below..

create a read only query / form to view the New records.

 

[Minty]

Okay you have a table in DB X called Master. In DB Y create a linked table to the Master table in DB X.

Create a form set to read only no edits for the linked Master table in DB Y. Save DB Y as an accde and you should have a live read only version of your Master table in DB Y.

 

[DevAccess]

but I need only records from database X from table 'Master Table' which has "NEW" as first three letters of the one of the column value.

The database Y is backup kind of database it has to have records even if gets deleted from db X.

 

[DevAccess]

May be i need to find out KEY column which would stop from being duplicate records in Y database as everytime this runs it should not copy existing records again and again.

 

[DevAccess]

what I am thinking is to have below proposed solution, though I am not sure

I will write query which would be done in X database's Master table to match column field value to see if it is equal to NEW then I will get recordset based on this query and then I will inner loop into Y database ( backup database ) and see if any of the incoming recordset of X datbase matches then skip otherwise will copy into Y database.

Is there any COPY functionality of recordset available ?

OR

what would be other logic you can suggest ?

Thanks
DJ

 

[Minty]

Make the record source for your form in DB Y a query that only shows NEW records...? Simple solution ?

 

[DevAccess]

This web page might help with the going from X to Y part. I suggest using the query designer to get an append query working the way you want in X and then use the information in the web page to extend the query to Y. To get records whose field values start with "New" you will use the LIKE condition in your criteria. Far as having this query run at intervals you can run the query from a form's timer event.

Hi Sneburg,

Thanks for your answer, but when I am querying with database Y , inserting link table into that it says you can not modify link table as it is read only.

Thanks
DD

 

[Gasman]

I don't think you are giving the people trying to help you enough information?
As Jdraw normally asks, explain in layman terms what you are trying to achieve.

People are offering valid suggestions, which then cannot work properly as another snippet of information is supplied.

If you supply all the relevant info in one go, people here will be able to offer you any number of solutions, but it is hard to do it piecemeal.

FWIW you were advised to be in Y an pull from X, not be in X and push to Y, but again that might not solve your problem.

 

[static]

currentdb.execute "SELECT * INTO backuptable" & format(date,"yyyymmdd") & " IN 'Backup.accdb' FROM MasterTable where status='new'"

should create a new table with the date appended every time it runs

or change to insert into and remove the date for single table

w3schools.com/sql/sql_select_into.asp

 

[DevAccess]

Hi Static, thanks for help but below is the SQL query which works on SQL server 2012, I need similar query from Access side in Database X where master table resides

INSERT INTO  table_2_temp YDatabase.DBO.table_2_temp
SELECT NAME,AGE,STATUS 
FROM table_1_temp T
WHERE STATUS='NEW'
AND  NOT EXISTS 
(SELECT NAME FROM table_2_temp  T2
WHERE T2.STATUS = 'NEW' AND T.Name=T2.NAME
)

however I don't know how I can run this query on perticular time frame say @ everyday or every one hour.

 

[static]

Create a macro that
1. runs your query
2. closes the database

name it autoexec

a macro named autoexec will fire whenever the database loads

then create a Windows scheduled task to open the database.

 

[DevAccess]

but above query would work for MS access as well ? I mean do we have same syntax in MS access as well. ?

 

[Minty]

I'm still completely at a loss as to why you can't just link to the table and use this query as the record source;

SELECT NAME,AGE,STATUS
FROM linkedMasterTable
WHERE STATUS='NEW'

And display this information in a read only form?

 

[static]

^ I'd guess new records won't always be new and wants a back up of them while they are.


SQL is SQL but you'll need to change the database obviously.

INSERT INTO  [c:\Y.accdb].table_2_temp
SELECT NAME,AGE,STATUS 
FROM table_1_temp T
WHERE STATUS='NEW'
AND  NOT EXISTS 
(SELECT NAME FROM [c:\Y.accdb].table_2_temp  T2
WHERE T2.STATUS = 'NEW' AND T.Name=T2.NAME
)

 

[DevAccess]

I'm still completely at a loss as to why you can't just link to the table and use this query as the record source;

SELECT NAME,AGE,STATUS
FROM linkedMasterTable
WHERE STATUS='NEW'

And display this information in a read only form?

Hi Minty, thanks for your help.. but I dont know how to display information in read only form.. Can you please help me step by step ?

Thanks
DD

 

[DevAccess]

^ I'd guess new records won't always be new and wants a back up of them while they are.


SQL is SQL but you'll need to change the database obviously.

INSERT INTO  [c:\Y.accdb].table_2_temp
SELECT NAME,AGE,STATUS 
FROM table_1_temp T
WHERE STATUS='NEW'
AND  NOT EXISTS 
(SELECT NAME FROM [c:\Y.accdb].table_2_temp  T2
WHERE T2.STATUS = 'NEW' AND T.Name=T2.NAME
)

Hi, thanks for your help I will give this a try.