[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to create a mirror of a linked table in MS ACESS.

Posted on 2016-10-17
5
Medium Priority
?
113 Views
Last Modified: 2016-11-03
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!
0
Comment
Question by:Victor Nares
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41847360
I would create a small VBA function that:

disables warnings (set warnings false)
deletes contents
appends all records

I didn't understand your key comment
1
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41847381
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
1
 
LVL 22
ID: 41847388
> "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
1
 
LVL 39

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 41848858
I would use Crystal's suggestion to link to the data source in SSIS.


BTW, if you go the route of running an append or make table query and want to suppress warnings, I suggest turning the hourglass on when you turn warnings off and then when you turn warnings back on, turn the hourglass off.  I created two macros so I could type one instruction and never forget to manipulate the hourglass.  Having the hourglass on gives you a visual clue that the warnings are still off.  

Having warnings off is so dangerous that you have only to be bitten once to never, ever forget how dangerous that setting is.  Access has conditioned us to simply close objects and it prompts us to save if it determines that we have modified the object.  Well, if warnings are off, you don't get this warning so you can potentially lose hours of work as Access silently discards your changes because you left warnings off and forgot to specifically save before closing an object.
0
 

Author Closing Comment

by:Victor Nares
ID: 41872642
Thank you for your help!

I wound up finding an ODBC driver that allows SSIS to connect directly to Smartsheet.

Thanks again!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question