Importing with TransferDatabase from ODBC and including Indexes

#11

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

Quote:

David W. Fenton wrote:
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...server-express
-da tabase-to-another-computer

Wait a minute - the only thing keeping you from using the
backup-restore 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.

I don't want to make it dependent on the database, though, yes,
that's an option.

I've since decided to not use SQL on the target machine at all. It's
a real pain installing SQL Server Express 2008 -- lots and lots of
dependencies that have to be installed ahead of time, and on a
workstation, it requires TWO reboots.

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

#12

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

Quote:

Chuck Grimsby <cgat... (AT) gmail (DOT) com> wrote innews:bafddf93-9c6a-4c98-a1e8-82fe89a086d0 (AT) x25g2000yqj (DOT) googlegroups.co
m:
On Sep 1, 5:57*pm, "David W. Fenton"
NoEm... (AT) SeeSignature (DOT) invalid> wrote:
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/361
2149/
copy-sql-server-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?)?

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.

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).

There are a couple of free ODBC SQL databases that will run on a
USB key without a install, for what it's worth.

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

*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.)

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.

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.

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?

Sorry for the long delay in replying, I've been mostly unavailable the
for the past week.

The thought I had was based on a assumption that you had a Access
front-end attached to a SQL Server back-end. Since you didn't want
the users to use SQL Server Express, how could you allow them access
to the data in a highly mobile methodology. Thus the USB variations
to SQL Server that didn't require an install, yet could still be
worked through an Access front-end with just a re-pointing of the
front-end to the new back-end. Since both SQL back-ends (SQL Server
and the substitutions I mentioned) are SQL compliant databases, you
should be able to transport the data back and forth without too much
trouble, and would allow for the indexing of the data.

Did that not meet the criteria of your post? If this is not an
acceptable solution, I'd like to know why for my own future reference,
if not for the group's!

#13

Chuck Grimsby <cgatgoo (AT) gmail (DOT) com> wrote in
news:83c5f2ff-9a45-406f-b12e-4f7441d78fc7 (AT) k10g2000yqa (DOT) googlegroups.co
m:

Quote:

The thought I had was based on a assumption that you had a Access
front-end attached to a SQL Server back-end. Since you didn't
want the users to use SQL Server Express, how could you allow them
access to the data in a highly mobile methodology.

Either you did not read the cited statement of my situations
carefully, or you didn't read it at all:

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

Quote:

Thus the USB variations
to SQL Server that didn't require an install, yet could still be
worked through an Access front-end with just a re-pointing of the
front-end to the new back-end. Since both SQL back-ends (SQL
Server and the substitutions I mentioned) are SQL compliant
databases, you should be able to transport the data back and forth
without too much trouble, and would allow for the indexing of the
data.

Did that not meet the criteria of your post?

Not even close.

Quote:

If this is not an
acceptable solution, I'd like to know why for my own future
reference, if not for the group's!

It helps if you read the question you're responding to, which
includes the cited URL that provides all the background to the
question.

Gad, that's harsh, but I don't know what to say. Your followups are
really interesting, but really have nothing at all to do with the
question I asked.

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Importing with TransferDatabase from ODBC and including Indexes

comp.databases.ms-access comp.databases.ms-access

Re: Importing with TransferDatabase from ODBC and including Indexes - 09-03-2010 , 10:06 AM

Re: Importing with TransferDatabase from ODBC and including Indexes - 09-05-2010 , 09:36 AM

Re: Importing with TransferDatabase from ODBC and including Indexes - 09-05-2010 , 04:20 PM