February 18, 2019 at 4:56 pm
Hi All,
Currently i have an access front end with links to SQL tables. We now have a need to save the complete backened structure to an ACCBD file in the event of an SQL server failure.
can somebody please provide me with an example of how this might work. VBA friendly.
Cheers
February 18, 2019 at 5:39 pm
I wouldn't back up the structure to Access. There isn't a perfect 1:1 object mapping between the two. Why not just script out all the objects so you have all the create object scripts and save that to one or more T-SQL files?.
February 18, 2019 at 5:45 pm
The entire dataset is less than 2GB in size and if it were bigger I'd split the tables into two. Rght now, it's not that big.
I just don't know how to send all my tables to an ACCDB database.
February 18, 2019 at 5:52 pm
barry.nielson - Monday, February 18, 2019 5:45 PMThe entire dataset is less than 2GB in size and if it were bigger I'd split the tables into two. Rght now, it's not that big.I just don't know how to send all my tables to an ACCDB database.
If you can connect to your database from Access, you could run a bunch of MakeTable scripts in Access that get all their data from your SQL database. The only downside to that is that if you have a bunch of stored procedures, you'd have to copy the create object scripts to a memo field (or large text or whatever Access is calling them now) so that you can recreate all that. If all you need is data, you could dump it all to Excel. Access security is a total joke anyway.
February 18, 2019 at 5:59 pm
Thanks Pietlinden
So seems like you know my issue. Can you point me to a technical solution?
I use a number of SP's and views. I may have to replicate them into access queries.
One challenge at a time. let just start with the backing up of tables into an ACCDB.
Security is the least of my concerns. The data isn't that valuable.
Cheers
February 19, 2019 at 2:42 am
Why not just take standard SQL Server backups and in the event of a total outage, reinstall SQL Server somewhere and run a restore? Moving it all to Access is not going to be easy. Installing SQL Server is silly easy. Especially since we're talking about 2gb of data. That's within the limits of SQL Express, so you don't even need a license to get SQL Server back online. That's going to be way simpler to do and easier to maintain, than trying to automatically migrate it all to Access.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 19, 2019 at 4:12 pm
Hi Grant,
Thanks for the advice. My organisation keeps a dedicated SQL server someplace and I have been advised there is a possibility of it falling over from time to time.
It is on this SQL server that my DB backend is maintained.
So I have other drives available to me and if I could point to a file on those drives like I can with an ACCDB file then all would be okay.
I don't know how to do that with SQL.
Can i install SQL express on my local machine and copy the SQL backup to a local drive some place?
I can always have access point to the server as required in event of failure someplace.
Cheers
February 19, 2019 at 11:23 pm
Yeah, you can do exactly that. The only worry is if you're using enterprise features. However, that would be an even bigger worry if you tried to stuff that into access. If it's a standard database, then running it locally in express should just work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply