?
Solved

Access 2010 Data Macros

Posted on 2014-04-22
4
Medium Priority
?
987 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 38

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

777 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