Backup for testing purposes

[dan231]

Is it possible to make a backup to be used for testing?

I have an Access Front End with a SQL backend. I inherited this setup and have limited SQL experience. I do know that all the access tables/reports, etc. are shared from the SQL backend.

I need to experiment with some reporting as it's not correct and each time I make a change to a from the entire thing crashes company wide.

It's on a terminal services network with a batch file calling the access front end into each users profile. (not sure if this part is relevant)

Thanks,
Dan

 

[Rabbie]

Your set-up seems flawed. Normally only your data tables should be on your Back End. The Forms/reports should be held in the Front End DB. This will make it easier to test changes without crashing the whole system.

Each user should have their own copy of the FE database. There are several tools available to ensure people always have the correct version.

 

[dan231]

no, you're right, only the tables are shared. (I have double checked that)
But it does crash when I save a form.

 

[dan231]

If I understand this correctly, I need to copy my front end to a COPY of the production back end. This would let me make changes and not crash anything.

If that is true, do I need to get a 2nd SQL server? The SQL db file size is fairly large at 20GB and I don't have the space to copy it on the same server.

 

[gemma-the-husky]

two ways

you either need to set up another database in your sql server environment (not sure whether you can copy trhe existing somehow) and connect ot that.

alternatively make access copy tables of the sql database for testing purposes and develop against that - there may be odd operational differences between code for Jet and SQL databases ,though

 

[dan231]

I was afraid of that.

Thank you for the help and clarification.

 

[gemma-the-husky]

i am not sure exactly how easy it is to clone a SQL database - probably not too hard, but its not a windows thing.

not sure how you connect to the database - there are automated methods to connect to a SQL server - you just need to set the table connect properties correctly. I think there's a couple of ways (with DSN or without) - I am sure there are examples here

 

[dan231]

Thanks. I will look that up once I figure out how to get more space to do a copy.

 

[dan231]

Question about this.

For testing purposes, if the data isn't going to change, is it possible for me to just copy the FE, rename it and use this new file for testing forms, rpts and qrys?

 

[DCrake]

Here is a demo on switching backends during login.

http://www.access-programmers.co.uk/forums/showthread.php?t=170607

If you create a cloned copy of your SQL tables to Access and get it to link to the access tables when you login. In your case if you change Training to Dev then let access relink it to local files. Then do your work without affecting the live data.

David

 

[gemma-the-husky]

if you are not changing data in the backend, then just do a windows copy of your front end

now you can play around with the queries, forms etc to redesign your app - and distribute it whwn you are done


the problem is if you are trying to edit data, as you will be attached to your live data

 

[dan231]

Thank you all. I will check out that example and see if that'll work better then just a copy of the FE.