Using VBA to split a database

[georgedwilkinson]

I'm pretty sure someone on this forum said this can't be done. I want to split a database into FE/BE using Visual Basic code, even if it can't be done.

I'm convinced that, since I want it so bad, it should be doable.

So I researched and found:

DoCmd.RunCommand (acCmdDatabaseSplitter)

which runs without generating an error. That's not necessarily a good thing, though, since it seems to be waiting for a response from the user but I cannot find a dialog box.

Somebody (credible) please tell me that this is something that can be done (without lying). Or, if you must, tell me I'm barking up the wrong tree and it'll never happen, regardless of what secret application object stuff I find.

Has anyone ever successfully split a database via VBA code? Can it be done? Even if I want it really bad (if no)?

I'm using Access 2007 (tonight) on Vista and will try again tomorrow on Access 2002/XP. I also have an old copy of Access 2000 on Windows 2000 in mothballs, if needed.

How 'bout it, MVPs?

 

[pbaldy]

I'm not sure about using the splitter, but I think you could create a new db, export tables to it, delete the tables from the current db, then link to the tables in the new db. All in VBA. A brief test with a hard-coded table name suggests it could work.

 

[gemma-the-husky]

george - it obviously can be done in the way that paul says.

the other thing is that you can forget about the objects in the backend other than the tables, since you will never see them, so you could actually do this

1. copy the whole database to another location (either manually, or even while its in use form the active database - i'm sure that would work)
(rather than exporting the tables one at a time)

2. delete or rename the tables in the local database

3. link to the new back end


why do you want to do this? - is it to help with installation? - i can see that would be useful, although a bit awkward in a multiuser situation, as the backend may get overwritten be a second installation.

perhaps you could write a wizard to do it - although ive never tried to write my own wizards

 

[georgedwilkinson]

1. copy the whole database to another location (either manually, or even while its in use form the active database - i'm sure that would work)
(rather than exporting the tables one at a time)

2. delete or rename the tables in the local database

3. link to the new back end

I'll probably do exactly that.

why do you want to do this? - is it to help with installation? - i can see that would be useful, although a bit awkward in a multiuser situation, as the backend may get overwritten be a second installation.

I have a very large customer in Austin (who we all know and love). When I quote work to them, I keep the estimate as low as possible. In the first phase of this project, I didn't have time to split the database, and it was just a single-user pilot anyway.

Now they have significant data and want to share the database on their corporate network. It costs me about 8 hours to drive over there and I didn't budget 8 hours to go just to split the database. I figured I could write the code to split the db in less than 8 hours. One approach was to use the same code that Access uses...it looks like that's not gonna happen. My fallback was the method you suggested, which I'll probably be using.

perhaps you could write a wizard to do it - although ive never tried to write my own wizards

Sounds intriguing. Unfortunately at this point in my Access development career, I have no clue how to begin. But maybe I'll look into it next month when I have fewer projects going on.

Just as a side note, I really enjoy this forum and the excellent level of help available to just about everybody. I've gotten to know the personalities and I'm really enjoying working with all of you!

QQ