May 18, 2004 at 1:27 am
Hi everyone
I have to dump SQL server database to MS Access on the fly. I know that DTS is one solution but is there any other solution. I try to enumerate all the tables in the SQL server database and recreate them in MS Access but I don't know how to transfer the data from the table from SQL Server database to MS Access, if I go record by record it would be too slowly. Is there any way somehow to export tables from SQL Server to MS Access, somthing like upsizing wizard in MS Access but in oposit direction?
Thanks
May 18, 2004 at 5:21 am
Your options are DTS or writing a custom app. Either way thou it will still be record by record after the tables are created. There is no bulk task I am aware of to use with Access.
May 19, 2004 at 6:14 am
You could link to the tables using ODBC, and then loop thru them running a make table query for each, but DTS should be quicker
May 19, 2004 at 12:38 pm
Yeah, you should use DTS, that's going to be the fastest. Simply set up your SQL db as one data source and your access db as the other. Use a DTT to pump the data from one table to it's mirror.
There are a number of tools that will script out the objects in the database (QALite's pretty good); and you should be able to run this script in the SQL window of Access. Of course, any incompatibilities in the script will need to be modified, so you're looking at a pretty manual task overall.
Also, remember you can write tSQL statements against an ODBC data source, including access.
Signature is NULL
May 20, 2004 at 1:49 am
def use dts ! however, you will need to create the indexes yourself.
you can do it from access if you need to.
create a shell database with an autoexec macro that fires off a function with an ado query that queries the systables table in your sql database
select *
from sysobjects
where xtype = 'U'
and name not in ('dtproperties') -- add any other tables you dont want
loop through the recordset, and issue docmd.transferdatabase against each, remembering that the tables in sql with have dbo_ prepended on the names.
then have a little vba to explicitly set the indexes on the access db run from another macro say 'mcrCreateIndexes'
from access, just chain this from autoexec
if you used the dts 'push' method, in an activex script task have a filesystemobject command copy your empty template database with macros and code to a new location,
dts the data to it,
then run an instance of access with command switch /x mcrCreateIndexes.
May 20, 2004 at 3:49 pm
Would the import feature in Access be an option here? I've imported tables from SQL Server that were available via ODBC link.
May 23, 2004 at 11:47 am
Thank you every one for replayed. Becouse I have to do this within ASP, I don't have DTS and I am not very familiar with DTS objects to work them directly without the wizards. So I think I am going to do this with combination of ADOX ( to get table names and get/create relations) and for each table on SQL Server I will execute the next query in ADO "SELECT * INTO Test FROM [ODBC;Driver=SQL Server;SERVER=howareu;DATABASE=SQLSomeDB;UID=*;PWD=*;].TableForDump;"
May 24, 2004 at 1:02 am
the best solution for transfer data and Structure from SQL Server to Access is:
open Access
from "file" menu, choose "Get External Data" , then "Import"
then in "Files of type" , choose "ODBC DataBases"
then choose DSN name that is connected to your SQL Server .. or make a new DSN name to your SQL Server
then press "OK"
then choose the tables you want and click "OK"
and table will be transfered with data and structure
Note:
the relationship doesn't tranfsered .. you have to make it manually in Access again
I hope this help you
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply