Replication & Security

[sandra45]

Hi, I understand that Replication can help me in this scenario:
-I have two separate offices A and B;
-each month data in B needs to be sent to A for consolidation into A's database;
-B needs to send "only" each month's data for consolidation purposes, not the "WHOLE" data in B's database;
-B does not need to have A's data.

Can anyone tell me if Replication is the best way for this purpose? If not, any better suggestion and how?

Furthermore, for security issue, I have several tables in table group that contain sensitive data, e.g. I have 8 tables, and only 2 tables that are sensitive and should ONLY be opened by the Administrator. How to set these two tables only accessible to the Administrator but NOT accessible to User1, User2, User3, so on.

Thanks.

Regards,
Sandra

 

[VBslammer]

If B doesn't need to sync data with A, then replication won't be the best choice. Basically, you're talking about performing an import from B into A, and that's it. Replication performs 2-way synchronization, which means Access tries to make both databases mirror each other during replication. If you've used a Palm Pilot to "Sync" phone numbers between your PC and the Palm Pilot, it's the same principle - any changes made to the PC's version are sent to the Palm, and any changes made to the Palm are sent to the PC.

Another thing about replication is it adds complexity to your tables. Several fields are added to each table to track conflicts and the like, and autonumber fields change to random number fields which is troubling the first time you see it.

If the data in B's database is not duplicated in A's database, a simple export from B would do the trick. You could export the data to spreadsheets using a query to filter by month, then import append it into A and archive the spreadsheets.

If your offices are networked, you could import directly from B to A using TransferDatabase():

  DoCmd.TransferDatabase acImport, "Microsoft Access", "G:\OfficeB\acct.mdb", acQuery, "qryMonthSales", "SalesImport", False

As far as security, you'll have to setup user-level security, which means every user will have to login to use the database. You can set up Groups that have restricted access and then assign users to those Groups. It's a lot of work, but you can fine tune the permissions for each object in the database if you know what you're doing.




VBSlammer

Unemployed in Houston, Texas

 

[VBslammer]

To import the resultset, rather than the query itself, change [tt]acQuery[/tt] to [tt]acTable[/tt]:

DoCmd.TransferDatabase acImport, "Microsoft Access", "G:\OfficeB\acct.mdb", acTable, "qryMonthSales", "SalesImport", False

VBSlammer

Unemployed in Houston, Texas

 

[sandra45]

Hi VBSlammer, thanks for your tip. Does DoCmd.TransferDatabase create another table for the data instead of appending it to the existing table at A's? Even though we have two separate offices, we need all data to be stored in one table for consolidation purposes, NOT storing on different table for data from different office.

This DoCmd.TransferDatabase is used only by A right? What about at B's to export out the data, is it just changing acImport to Export?

Regards,
Sandra

 

[sandra45]

Our offices are connected to the internet but each database is stored on each office's server. We connect to the net via third party ISP company. Does it enable direct transfer data from office A to B or vv? Thanks.

Regards,
Sandra

 

[HFloyd]

Sandra,

Regarding your question about DoCmd.TransferDatabase, there are two arguements you can use to specify this information:

Syntax

DoCmd.TransferDatabase [transfertype], databasetype, databasename[, objecttype], source, destination[, structureonly][, saveloginid]

source: A string expression that's the name of the object whose data you want to import, export, or link.
(aka table name in "B" to import)

destination: A string expression that's the name of the imported, exported, or linked object in the destination database.
(aka table name in "A" after import)

So, you can specify a new name for the destination table, then create an Append query to copy the records into the correct table in "A"

Heather



Floyd Innovations

http://www.floydinnovations.com