Avatar of Chris Bottomley
Chris Bottomley
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Access database copy

I am about to embark on taking an existing database and adapting it.  Since the data is sensitive I would like to create a dummy for use here but be able to lift the real data from the original file into the public file for testing.

This question therefore is as follows:

If I have a db of several fields and want to upload data from different db, assume same data structures for now can you define a simple, ( to edit) way that db1 fields string1a int2a and date3a can all be copied across to db2 where the fields are string1b int2b and date3b.  The mapping between db fields may be completely unrelated so do not assume a naming convention ... This is so the db application can also be hidden!

Needs to be compatible with access 2003 through 2010 to permit different people to use and work with the db.

Chris
Microsoft AccessMicrosoft OfficeMicrosoft Applications

Avatar of undefined
Last Comment
JVWC

8/22/2022 - Mon
macksm

You will have to write acode in VB.nett and call two databases from it to do this. You ca'nt do it from Access or Acress VBA.
Chris Bottomley

ASKER
You will have to forgive my scepticism, I am sure I have seen use of queries for copying data between databases so for now I will await any other answers.

Chris
JVWC

Have you considered linking the tables (with the sensitive data) to the project file and then running code or queries on the linked tables to transfer data.
If your sensitive data and Project data are both in backend files, you can link to both...

Cheers
JC
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
peter57r

I'm afraid your Q doesn't make much sense to me.
Why would a copy of a database be using different field names to the original?

I don't know if this info is relevant but it sounds like it might be...
You can link to tables in other databases and to switch between say development and production backend files using the linked tables manager.

You can create an append query based on a source table and assign each selected  field to a specific field in the target table.
Chris Bottomley

ASKER
JVWC

Could be .... The key thing is how it will work give. For example how are two db's linked?

Peter57r

Your last comment sounds good how would an append query work for above sample data ... Which db must be open and what will query look like.  Note like I said in order to provide a db for future related questions I need to sanitise some field names to prevent the purpose which is sensitive.

Essentially therefore I want to be able to run the copy mechanism locally to test in the real db even whilst the experts are using different fields.  In those future questions I will need to map the different field names but for now it is simply about being able to copy real data into the ee version so I can do representative testing.

Chris
peter57r

What sample data?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
JVWC

If you Project is using local tables, simply link to the tables in your existing DB and code/query to transfer the data.
If you Project has a remote BE then you can link to both sets of tables (Old and new) in the same project FE with the same result.

Is this what you are after?

Cheers
JC
Chris Bottomley

ASKER
Peter

If I have a db of several fields and want to upload data from different db, assume same data structures for now can you define a simple, ( to edit) way that db1 fields string1a int2a and date3a can all be copied across to db2 where the fields are string1b int2b and date3b.  The mapping between db fields may be completely unrelated so do not assume a naming convention ... This is so the db application can also be hidden!

JVWC

At present I have two identical databases data in one nothing in the other, assuming I have just opened access ... No db open what do I do to copy the data?

Chris
JVWC

Are you using a split database(s) design?
Your help has saved me hundreds of hours of internet surfing.
fblack61
Chris Bottomley

ASKER
Since I have no idea what a split db is then no.  As stated I have two identical db and they sit on the hd as sourcedb.mdb and targetdb.mdb

Chris
JVWC

OK then.
Let's assume Your Targetdb is the new one you want to develop, and Sourcedb is the existing db which contains your current sensitive data.

Open Targetdb and create a link to the data in Sourcedb.
Then create a local table in Targetdb.

The local table and linked table will both appear (and work) as local tables.
Should you post your targetdb here, we would only see the local table and the data in that table. The linkdata would not work .

Take a look at this link to see more about split design. http://allenbrowne.com/ser-01.html
Essentially, the data tables live in one db file and the code, forms reports ect live in your project file.

Cheers
JC


Chris Bottomley

ASKER
Jc

Would the field names in the sourced be visible in targeted as this is a concern if so, appreciate the data is not displayed but in the linking will the mapped field names be visible in the target?

Chris
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott McDaniel (EE MVE )

One very simple way is to use the IN command directly in your SQL. You'd do this in VBA code:

Currentdb.Execute "INSERT INTO DestinationTable IN 'C:\SomeFolder\SomeDB.mdb' SELECT Col1, Col2, Col3 FROM YourLocalTable"

Note that DestinationTable must exist in your destination database, or you can use SELECT INTO, which is slightly different in syntax:

Currentdb.Execute "SELECT Col1, Col2, Col3 INTO DestinationTable IN 'C:\SomeFolder\SomeDB.mdb' FROM YourLocalTable"

This would build a 3 column table named "DestinationTable" in the SomeDB.mdb file with the data from YourLocalTable.

If you need to obfuscate that data, build a routine like this:

Function ScrubData(TableName As String)
  Currentdb.Execute "UPDATE YourTable Set Col1=Replace(Col1, 'a', '#'), Col2=Replace(Col2, 'a','$')"
  Currentdb.Execute "UPDATE YourTable Set Col1=Replace(Col1, 'e', 'b'), Col2=Replace(Col2, 'e','}')"
  Currentdb.Execute "UPDATE YourTable Set Col1=Replace(Col1, 'i', 'x'), Col2=Replace(Col2, 'i','@')"
  Currentdb.Execute "UPDATE YourTable Set Col1=Replace(Col1, 'o', '^'), Col2=Replace(Col2, 'o','v')"

  '/ and so on until you're comfortable with the munging

End Function

Run this after you've inserted your data into the destination table (and obviously run it on that destination table, not your "live" table)
SOLUTION
JVWC

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Chris Bottomley

ASKER
Lam

Looks pretty close but still would like the field names to Be different between the two, is that possible?

Chris
Chris Bottomley

ASKER
Jvwc

Will look later ... Using iPad at the mo.

Chris
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Chris Bottomley

ASKER
JC

I see now, using the queries then all 'work' is done on new roué teats and I simply seed this table according to my stage .... Delete and import secret for me to test and delete then import sample for ee uploads?

Presumably the three table structures need to be maintained in parallel so i will awAit LSM response but I am assuming for now I can edit the queries to reflect the 'secret' field names in supersecret but then as such they will still be visible.

Note I appreciate i can use meaningless names to obfuscate the field names but for the usability this makes no sense and for info that aspect will be dropped if too hard.

I do like however the structure as it then easy to send the public file to my colleague to test usability without compromise to the data.

Chris
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chris Bottomley

ASKER
The preferred solution keeps the db structure in one place making maintenance easier and simply works.  The alternative of multiple tables does indeed work just a little harder to maintain so my thanks to you both, I have something to build upon.

Chris
JVWC

Back now,
Chris, my example was just a quick knock up to show how you might link some data and use a local table for development. IE: to show the concept of my suggestion.
This also demonstrates very basic features of split db design.
You could also move the local tables to yet another db such that the development db sees data from multiple "backend" DBs.
I stopped short of using code to move the data in my example, but one could easily combine the delete query with the apend query to clean out a table and re-populate, using code.

In terms of transferring data, I would happily defer to higher authorities such as LSMs suggestions.
There is much more finesse involved there.

Cheers
JC
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.