What's the fastest way to

[JPnyc]

dump the entire contents of a SQL server DB into a .mdb?

[RhinoBull]

In majority cases this won't be possible due to MS Access limitations: max size, types conversion, stored procs, etc, etc, etc ...

[Shaggy Hiker]

It seems to me that the fastest way would be to open the db in Access, and save as an .mdb. I'm not sure that there is a significant problem with this. You can certainly open SQL Server DBs in Access, assuming you have proper permission. There may be some goofy conversion of data, but that's life.

[RhinoBull]

Originally posted by Shaggy Hiker
It seems to me that the fastest way would be to open the db in Access, and save as an .mdb...

Just out of curiousity: how would you do that?
How can you import all stored procs, triggers, sequences, etc in Access? In other words how can you import objects that have no identical (or similar) representation in Access environment?

[Dave Sell]

Originally posted by RhinoBull

How can you import all stored procs, triggers, sequences, etc in Access? In other words how can you import objects that have no identical (or similar) representation in Access environment?

That depends on how they will be used again in the future. Are you looking to backup a SQL db to an Access db to convert back to a SQL db later? Or are you looking for Access equivalents to the SQL objects that have no native representatino in Access?

[RhinoBull]

I wasn't really asking those questions - I personally know the answers. I was hopping that Hiker would somehow explain what he had in mind ...

[RobDog888]

Perhaps he was just thinking of importing tables into Access and
then saving the Access db?

[JPnyc]

Well I know there are some datatypes access has the SQL doesn't and vice versa, but I think most of what we need are chars, or nChars as they're called.

[Shaggy Hiker]

I was just thinking of moving tables and some relationships. Basically, you are going from a large set to a small set. If you can't accept the loss of many features, you are bound for disappointment.

Since he was going from larger to smaller, I assumed that the loss of those things that wouldn't migrate was already acceptable to him.

[RhinoBull]

Originally posted by Shaggy Hiker
I was just thinking of moving tables and some relationships. Basically, you are going from a large set to a small set. If you can't accept the loss of many features, you are bound for disappointment.

Since he was going from larger to smaller, I assumed that the loss of those things that wouldn't migrate was already acceptable to him.

... HOPEFULLY ...

[JPnyc]

Thanks all. The contents of the SQL db were migrated from access, so the size shouldn't be a problem YET. The differing data types could be, but as I said I think most are nChars. I was thinking of a quick VB app that would run in windows scheduler and dump the entire SQL db into the mdb weekly. We already have backup, but this would allow the site to be back online in about 10 minutes.

[Shaggy Hiker]

I'd just go with ADO, and not worry quite as much about speed. Were I to be writing that, my biggest worry would be data compatibility. The advantage of using a program to handle this is that you could do some conversion as an intermediate step for those fields where that would be important.

Simple in code, may not be either particularly fast, or particularly robust.

[JPnyc]

Well I know basically how to write the function, just wondered if there was a batch command that I didn't know about, and one that would overwrite existing records in the mdb. I really didn't relish writing a few conditions and update or insert statements depending on the condition. I just wanted one condition that overwrites the entire table of the mdb. In other words, I'm being lazy.

[JPnyc]

So are there any special commands for this in VB that I dunno about or do I just iterate through the records the old fashioned way and use insert/update statements?

[JPnyc]

I guess not, eh?