Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Access 2010 Data Macros

Posted on 2014-04-22
4
Medium Priority
?
1,009 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 59
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 40

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
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.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

571 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