unsplit database

[mloucel]

I don't know why you would want to but the easiest way to get the db back together again is to delete the links to the be tables and then use the file/get external data to import them.

HA.. you're a genius Pat, I just did it and it worked, now I have 2 copies, my screwup test all copy, and my splatted, when I update a form a query, etc, I do it in my screwup copy, once it works, I go to the split, import the updated modules, and voila, problem solved.

Thanks, for the lesson.

 

[isladogs]

If you ever need to do this again, I recommend an even easier method of unsplitting (aka splatting) mentioned earlier in this old thread.
Right click on each of the linked tables and select Convert to Local Table. If you have a lot of linked tables, you can loop through and convert using VBA.

 

[mloucel]

If you ever need to do this again, I recommend an even easier method of unsplitting (aka splatting) mentioned earlier in this old thread.
Right click on each of the linked tables and select Convert to Local Table. If you have a lot of linked tables, you can loop through and convert using VBA.

Holy CRACK Colin, it's been there all the time, and I have never seen it, darn, no wonder you are an MVP / VIP or should I say MID {Most Important Developer}
that is the easiest.

 

[plog]

Dang it. Missed it by 4 years.

 

[mloucel]

Dang it. Missed it by 4 years.

I was waiting to see yours....

 

[isladogs]

@mloucel
Ha! Unwarranted flattery will get you nowhere everywhere! It’s certainly been available for many years yet for some reason doesn’t seem to be widely known about.
I’m not sure about the MID acronym as i immediately reversed it! DIM Isladogs as … (insert your choice of words here)

 

[mloucel]

@mloucel
Ha! Unwarranted flattery will get you nowhere everywhere! It’s certainly been available for many years yet for some reason doesn’t seem to be widely known about.
I’m not sure about the MID acronym as i immediately reversed it! DIM Isladogs as … (insert your choice of words here)

Darn
I'm
Marvelous

will that work?

 

[isladogs]

Ha! Nicely done but my wife (and many others...) certainly wouldn't agree with that

 

[Pat Hartman]

HA.. you're a genius Pat, I just did it and it worked, now I have 2 copies, my screwup test all copy, and my splatted, when I update a form a query, etc, I do it in my screwup copy, once it works, I go to the split, import the updated modules, and voila, problem solved.

But WHY? This is a bad idea and unnecessary. If you are afraid you will mess up and test with the production BE, then add some code to it to warn yourself if the FE is running on your computer but is linked to the production BE. I have code that turns the header section of a form a funky color like lime green when I am using a FE linked to the production BE.

 

[mloucel]

But WHY? This is a bad idea and unnecessary. If you are afraid you will mess up and test with the production BE, then add some code to it to warn yourself if the FE is running on your computer but is linked to the production BE. I have code that turns the header section of a form a funky color like lime green when I am using a FE linked to the production BE.

the db is still not production, and I am planning to use a NAS drive instead of the server, even later IF I LEARN ENOUGH, I will move the BE to SQL SERVER, I found somewhere that there is a piece of software that moves all the tables to SQL with min effort, and apparently works pretty good, so for now I need it here with me, in my cozy PC, once is completely working with at least 99% accuracy and my boss is happy, I will send it to the NAS, for people to play around, meanwhile I will do my best to move it to SQL, we have [or I HAVE] until January, so plenty of time.

Thou your code as you mention is tempting..

 

[isladogs]

I found somewhere that there is a piece of software that moves all the tables to SQL with min effort,

SQL Server Migration Assistant AKA SSMA

 

[GPGeorge]

SQL Server Migration Assistant AKA SSMA

AP: Migrating Access Tables to SQL Server, Comparison of Methods w/Tom Wickerath and George Hepworth

 

[gemma-the-husky]

i recently came into the same situation described in this thread. i have a follow up question to this. is there any way to get both the FE and BE into a single application (unsplit)? so basically i'll only have one .MDB file?

thanks for any suggestions everyone.

If you do that you are back to the same issue. It's hard to develop new facilities as you are always working on the live database.

If you split it you can develop using a test version of the database with a test version of the data. When the work is completed, you can release the test version as a new live database, and just update it to point to the live data. You may need to add new tables or fields to the back end database. There really is no reason not to split a database, unless you are going to be the only user.

 

[Gasman]

There really is no reason not to split a database, unless you are going to be the only user.

I always split mine now, and I am the only user. That way if I muck up the code side of things, my data is safe?
I can always go back to a backup for the code, but the data would not be the same.

 

[Pat Hartman]

During early development, when the tables are somewhat in flux, it is more convenient to develop with embedded tables regardless of whether they will ultimately end up as SQL Server or ACE once split. This is because the Access environment gives you some help with propagating changes. But, once you are ready to deploy, you must split and once done can never go back. At that point, you shouldn't be changing column and table names and so the built in Name Auto Correct feature isn't available but that shouldn't be a problem because at that stage in the development, you're still changing names, you've got bigger problems.

Anyway, even apps I build for myself are split if there is any permanence to the data. I use a relinking routine so that when the db opens, it makes sure the app is linked to the BE that is in the same folder as the FE. That way if I use the app from a thumb drive, it automatically links even when the drive letter changes because the drive is plugged into a different computer.

 

[mloucel]

During early development, when the tables are somewhat in flux, it is more convenient to develop with embedded tables regardless of whether they will ultimately end up as SQL Server or ACE once split. This is because the Access environment gives you some help with propagating changes. But, once you are ready to deploy, you must split and once done can never go back. At that point, you shouldn't be changing column and table names and so the built in Name Auto Correct feature isn't available but that shouldn't be a problem because at that stage in the development, you're still changing names, you've got bigger problems.

Anyway, even apps I build for myself are split if there is any permanence to the data. I use a relinking routine so that when the db opens, it makes sure the app is linked to the BE that is in the same folder as the FE. That way if I use the app from a thumb drive, it automatically links even when the drive letter changes because the drive is plugged into a different computer.

Thanks @Pat Hartman and everyone else, I see your point, just a little reminder, most of us that ask to undo the split is because we are newbies, the knowledge you all have is of utmost importance, to me at least, is a moment to learn, a way to understand that undoing the split is unnecessary, but in our eyes and poor understanding of how thinks work, makes it harsh to see your opposition, we do want to believe that we are in the "RIGHT" when we are in the "WRONG".
Your logic makes sense to me now that I have seen your post, and I have actually gone back to the split DB backup I have, and will continue working on that version, this is not the final, production version, that one is coming in January, for now everything is a test.

I really appreciate your words of wisdom, same as with @arnelgp and @isladogs, @gemma-the-husky , @theDBguy, and so many others I haven't mentioned, and I take them as a piece of your knowledge that goes into my book, thank you as well for being so patient and put-up with my sometimes-non-sense questions, you and all those that have helped me, are a vital part of my learning experience of ACCESS VBA.

Maurice.

 

[Pat Hartman]

I think one of the hardest things for inexperienced developers is to understand the need for a safe development environment. You need to be anal about keeping your test versions of the FE and BE isolated and backed up. You need to keep versions of your work. Once a process is working. Save and make a backup. Then start with the next change. I can't tell you how much work I have lost over the years by being sloppy with this. It is some of this personal pain that can come through as we try to get you to make good design decisions. We don't want you to discover the hard way why we insist on certain techniques.

 

[mloucel]

AP: Migrating Access Tables to SQL Server, Comparison of Methods w/Tom Wickerath and George Hepworth

I saw the video finally, that is just outstanding, but means, is not easy as I thought it will be, you were able to fix the nulls since you know HowTo and WhereTo, but a layman as me will be lost as cannibal in the middle of Manhattan , but nevertheless I can always come here and ask.

 

[GPGeorge]

I saw the video finally, that is just outstanding, but means, is not easy as I thought it will be, you were able to fix the nulls since you know HowTo and WhereTo, but a layman as me will be lost as cannibal in the middle of Manhattan , but nevertheless I can always come here and ask.

The thing is that you CAN come here to ask for information on solving any specific issues that come up.

One reason I like SSMA is it produces reports prior to completing a migration. You have a lot of information to go onto assist with the migration.

 

[Pat Hartman]

When I do conversions using SSMA, I always fix the conversion problems in ACE and then reconvert. It makes the whole process go more smoothly.