Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access 2010 Data Macros

Posted on 2014-04-22
4
Medium Priority
?
1,000 Views
Last Modified: 2014-04-30
Hopefully just a quick question with a quick answer. I have searched the interweb and cannot find a direct answer.

I am trying to determine if the CreateRecord Data Action when used in any of the After Insert/Update/Delete Data Macros can write to a table that is not in the same database.

I am guessing not because I have tried to a linked table in both Access 2010 and SQL Server and the Data Macros do nothing. If the table is local to the database then all is fine.

None of the documentation I can find explicity states one way or another - just that "this data actions writes a record to the named table" (or words similar).

I am trying to have a central table that holds details of changes made in several databases (the created record would hold the DB Name, Tablename, and PK of the affected record). The changes log would hold records with unique id's (Autonum).

This way our occasionally connected apps would need to look at one DB and one table only when they are on the network to know which other databases/tables/records need looking at. This way we minimise the connections and data transfers.

If we cannot have a central table, we will have to hit every database every time because each DB will need to have its own changes log.

Thanks,
Matt
0
Comment
Question by:matthewgreen
[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
  • 2
4 Comments
 
LVL 58
ID: 40015307
Matt,

 I can't answer this with 100% certainty, but all the new table level macros were written with web databases/apps in mind, so I strongly doubt that would take into account writing to anything other than a local table.

 They are all also "table level triggers", so again, they were designed to be working with local tables.

 I haven't worked with data macro's  all that much because I detest the editor, but I'm pretty sure what you've found is correct.

 Someone else may jump in with a better answer, but I believe you cannot.

Jim.
0
 

Author Comment

by:matthewgreen
ID: 40015337
Thanks Jim - bit of a pain, and I have to agree with you the editor is vile beyond words! Just thought this would be a natty solution to a problem I was given to solve (not wanting to go the replication route as that seemed a bit hammer and nut for this problem).

I suppose it is no great shakes to have to open each DB in turn and check the log, just would have been nice knowing you were not opening a link for no reason.

This is only a stopgap anyway as we will untimately be dumping Access for .NET app w/ SQL Server databases and Synch Framework.

Thanks,
Steve
0
 
LVL 39

Accepted Solution

by:
PatHartman earned 600 total points
ID: 40018708
You can convert to SQL Server without dumping your Access FE.  Access makes a fine FE for SQL Server or other relational databases and even a less than perfect Access client/server app will be far better and significantly cheaper than any web app.  Of course, if you were intending to build client/server apps, you can build more professional apps than you can with Access since MS has not seen fit to upgrade the controls for Access in many years.  

I'm assuming that you don't want to actually go into each form and do the logging there which is why you are looking at Data Macros in the first place.  I would create a separate table in each database.  Then to solve the "one place to check" problem, I would link to all the log tables in the various databases and create a union query that returns the last update for each of the linked tables.  This isn't as clean as updating a single table but it does give you a single point to check from the remote databases.
0
 

Author Closing Comment

by:matthewgreen
ID: 40032336
This is a perfectly adequate solution to meet our needs at the moment, and in essence achieves the original plan as there is one database to go to to get the data.  Adds an extra layer of Access (linked tables) and a union query, but will work .... as long as I do not do something stupid and make the query invisible to .NET .... but that is my challenge!

Thanks PatHartman
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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. …

604 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