I've been trying to figure out how to make a copy of a database
newly upsized to SQL Server, and asked how to do it on
StackOverflow.com:

http://tinyurl.com/26yhxuu =>
http://stackoverflow.com/questions/3...ver-express-da
tabase-to-another-computer

I have concluded that it's probably going to be a lot easier to just
import the data into an MDB file and then copy that to the USB drive
for transport. It then won't require installation of SQL Server on
the user's computer, for instance.

I've already written code to import the ODBC tables (details not
important), but the show-stopper is that it's not importing indexes,
so these tables will be real dogs for data analysis.

Is there some way to force TransferDatabase to import indexes from
ODBC? I'm concluding that there isn't, so I'm going to have to
instead import the data with INSERT queries into an empty template
database.

Does everyone agree that it's impossible to import the indexes
(without mucking about with metadata about the ODBC database schema
and translating it into Jet indexes?)?

[and another thing I discovered in doing this is that the
TransferDatabase help file has an error in it. Where it lists the
valid values for the second argument (DatabaseType), it reads "ODBC
Databases". It should be singular, "ODBC Database". Lots of people
have had an error with that, as it produces error 2507, and I didn't
encounter any explanation anywhere of how to resolve it. If you do
the same thing in the macro designer, you'll see the correct list of
values]

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

On Sep 1, 5:57*pm, "David W. Fenton" <NoEm... (AT) SeeSignature (DOT) invalid>
wrote:
I don't have a lot of experience with the TransferDatabase command
myself. I always found it too slow. I do remember however that with
lots of data, it's faster to transfer the data without the indexes
then add them in after the transfer is complete.

There are a couple of free ODBC SQL databases that will run on a USB
key without a install, for what it's worth. SQLLite is one of them
(Firefox uses it as quite a few other programs), and I think MySQL
will run from a USB Drive without an install as well. I remember
seeing a web development portable (thumb drive) app that included a
portable SQL Database, but I can't remember the name of it. Try
portableapps.com or protablefreeware.com It was probably on one of
those. Since they are ODBC compliant, Access should be able to link
to them. (I know I can do it with SQL Lite, though you have to
download the ODBC Drivers.)

The above said, I can't say as I'd recommend running the database on a
Thumb drive. An external USB drive (with actual spinning platters) is
*much* faster. USB Keys are great for transport, but other then that,
they suck for database work. You can get external USB drives that fit
into your pocket as well. They're bigger then a USB Key, but the
speed is worth it. Pop TrueCrypt (in traveler mode) on them and
you've got a nice secure method of moving data around *and* using it
directly off the drive at a nice fast speed.

Chuck Grimsby <cgatgoo (AT) gmail (DOT) com> wrote in
news:bafddf93-9c6a-4c98-a1e8-82fe89a086d0 (AT) x25g2000yqj (DOT) googlegroups.co
m:

It's not what I consider a lot of data (about 29MBs compacted --
that's data from 1989 to 2008, so it shows that it doesn't really
grow that much).

The indexes have to be added back in for the tables to be usable, so
I don't really see how importing with no indexes then adding the
indexes is going to be preferable to just having the indexes in
place and running the import. Surely the overhead for the insert is
going to be pretty much equal to the amount of time it takes to
write the indexes upon creation (or close enough not to matter
much).

This is interesting, but I'm not seeing how this addresses the
problem I'm trying to solve.

I can't see how running it from a USB drive makes it any easier for
me to copy the data from the SQL Server to the users PC for
statistical analysis.

All very interesting, but I must be missing something: how do you
think all that addresses my question? Or were you just free
associating with some highly useful tangential information?

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

Have you considered using the osql utility to backup and restore the
db?. I think it's available when SQLServer is running.

Log into the command prompt as administrator.

The following command creates a backup of database "FFDBA" on Server
"VOSTRO-EXPRESS\SQLEXPRESS" using Windows Security (-E) in the default
backup directory of the server. It takes about two seconds here.
Depending on Windows the SQLServer account may have very limited write
permissions and just using its default backup directory, by not
specifying any folder at all, may be easier than extending those
permissions. The default backup directory on my computer here is C:
\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup.

osql -S VOSTRO-EXPRESS\SQLEXPRESS -E
USE MASTER
GO
BACKUP DATABASE FFDBA TO DISK='FFDBA.BAK'
GO
Exit

After copying the backup file to the new computer one can install it
so to speak using the same kind of commands.

RESTORE DATABASE FFDBA FROM DISK = 'C:\FFDBA.BAK'
Read permissions may not be as restricted as write permissions.

One can write these T-SQL commands to a simple text file and "execute"
that from osql; I suppose one could run these from Access using the
Shell command but I haven't done that.

The backup has everything worth copying I believe. It can be
compressed.

On Sep 1, 6:57*pm, "David W. Fenton" <NoEm... (AT) SeeSignature (DOT) invalid>
wrote:

David W. Fenton wrote:
And nobody mentioned sp_detach-db and sp_attach_db?

No, TransferDatabase ignores indexes

Yes


You can detach the database from the server (using sp_detach_db), copy
the mdf and ldf files to your thumb drive, use sp_attach_db to reattach
the them to the server, carry the thumb drive to the other computer copy
them onto the destination drive and use sp_attach_db to attach them to
the destination server

Alternatively, you can use backup and restore as Lyle suggested.

--
HTH,
Bob Barrows

Ah! I should have read the thread in the link to see why my suggestion
was not acceptable. Never mind.

Bob Barrows wrote:
--
HTH,
Bob Barrows

"Bob Barrows" <reb01501 (AT) NOyahoo (DOT) SPAMcom> wrote in
news:i5oes0$8il$1 (AT) news (DOT) eternal-september.org:

For what purpose? And how could it be used with all the conditions
involved?

For ODBC, but not for Jet/ACE.

Did you read any of the comments at all? It would appear not. This
is exactly what I suggested in the first place, but the point of
posting the question was to figure out HOW. The problem is that
doing so requires so many dependencies that I've concluded it's
probably easier to just create any MDB file from the data, copy it,
and then the target workstation doesn't even need SQL Server
installed.

So, as of this point, I'm going to create an empty shell database
with empty indexed tables, then insert the data into that. I hope it
isn't significantly slower than the plain import.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

"Bob Barrows" <reb01501 (AT) NOyahoo (DOT) SPAMcom> wrote in
news:i5ofl2$cjp$1 (AT) news (DOT) eternal-september.org:

That's why I posted the link, to save having to recapitulate
everything that had already been discussed.

As I said in my previous reply, it seems obvious I'll have to import
the records into a shell database, with empty, indexed tables. It
remains to be seen how long this takes in comparison to the
TransferDatabase import.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

David W. Fenton wrote:
option is the lack of client tools on the client's machine? it's just
occurred to me that since the client has Access on his machine, you can
use VBA to run a passthrough query to run the sql statement to restore
the database on his SQL Express server, can't you? Take another look at
answer #4.

--
HTH,
Bob Barrows

Along the same line use VBA to create an ADODB connection and backup
and restore with T-SQL. If the computers are accessible on the LAN one
could probably backup directly to the "receiving" computer after a
little finagling with permissions. And I think, but am not sure,...
then connect to the receiving computer's server and restore the db,
all from the parent application ... maybe ... perhaps.
Properly programmed it should be just an automated procedure that runs
on the click of a button and unless the db is huge, requires only
seconds to complete.

On Sep 2, 2:40*pm, "Bob Barrows" <reb01... (AT) NOyahoo (DOT) SPAMcom> wrote: