Solved

Access 2010 Data Macros

Posted on 2014-04-22
4
941 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
  • 2
4 Comments
 
LVL 57
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 34

Accepted Solution

by:
PatHartman earned 200 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

705 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now