Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1019
  • Last Modified:

Access 2010 Data Macros

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
matthewgreen
Asked:
matthewgreen
  • 2
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
matthewgreenAuthor Commented:
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
 
PatHartmanCommented:
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
 
matthewgreenAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now