asked on How to create a mirror of a linked table in MS ACESS.
Thank you all in advance!
We have a linked table in MSACCESS. It is linked to a data source hosted by Smartsheet. While I am able query the table in MS ACCESS and pull back real time data, I am not able to see the table when creating an ODBC source in SSIS. Thus, I am unable to import realtime data to SQL. However, when I make a "static" copy of the table, I can see the table from SSIS no problem...except the data is not current.
How can we keep the data in the static table current without setting off MS ACCESS notifications? Since the static table (the copy of the linked table) has a key, Running append queries against the Static Table will generate notifications each time a macro kicks off the append. I understand their is way to write some vb code to pull this off but am not clear on how. Do I create vb code for the append query or for the macro kicking off the query...or both?
Thank you all in advance!
We have a linked table in MSACCESS. It is linked to a data source hosted by Smartsheet. While I am able query the table in MS ACCESS and pull back real time data, I am not able to see the table when creating an ODBC source in SSIS. Thus, I am unable to import realtime data to SQL. However, when I make a "static" copy of the table, I can see the table from SSIS no problem...except the data is not current.
How can we keep the data in the static table current without setting off MS ACCESS notifications? Since the static table (the copy of the linked table) has a key, Running append queries against the Static Table will generate notifications each time a macro kicks off the append. I understand their is way to write some vb code to pull this off but am not clear on how. Do I create vb code for the append query or for the macro kicking off the query...or both?
Thank you all in advance!
Microsoft AccessMicrosoft SQL Server
Last Comment
Victor Nares
docmd.set warnings false
docmd.runsql "delete * from static table"
docmd.runsql "insert into static table select whatever from source table"
the data will only be as current as the last run
docmd.runsql "delete * from static table"
docmd.runsql "insert into static table select whatever from source table"
the data will only be as current as the last run
> "I am not able to see the table when creating an ODBC source in SSIS"
add a link to the SSIS table(s) in the database that links to Smartsheet. To be able to know what was added and updated since you last synchronized*, add tracking fields to the data structure:
dtmAdd, date/time, default value =Now()
dtmEdit, date/time, default value =Now() -- update with code when information changes. If a form is used, you can use the form BeforeUpdate event.
* you will also need to store the date/time each time data is synchronized
add a link to the SSIS table(s) in the database that links to Smartsheet. To be able to know what was added and updated since you last synchronized*, add tracking fields to the data structure:
dtmAdd, date/time, default value =Now()
dtmEdit, date/time, default value =Now() -- update with code when information changes. If a form is used, you can use the form BeforeUpdate event.
* you will also need to store the date/time each time data is synchronized
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you for your help!
I wound up finding an ODBC driver that allows SSIS to connect directly to Smartsheet.
Thanks again!
I wound up finding an ODBC driver that allows SSIS to connect directly to Smartsheet.
Thanks again!
disables warnings (set warnings false)
deletes contents
appends all records
I didn't understand your key comment