CODEWITHSUNDEEP
sql-serverms-accessbatch-file
Dinesh Kumar
Dinesh Kumar

Reputation: 101

How to copy .mdb file to .mdf file

I want to copy the ms access database(.mdb) to sql server database(.mdf). I did it with sql server Import and export data service. But I want it copy the data regularly or a specific time. Is it possible or not. I have tried to create a batch file 

copy /y "E:\Dinesh Work\for-reports.mdb" "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\for-reports.mdf"

but it gives the following error:

The process cannot access the file because it is being used by another process.

I have same tables in my sql server database as msaccess database. Is there any solution with batch file or something else.

Any suggestion will be appreciated.
Thanks in Advance.

Upvotes: 1

Views: 6593

Answers (3)

Christian Specht
Christian Specht

Reputation: 36421

This sounds like you want to use the Access database to edit the data in the SQL Server database.
Is that correct?

If yes, do you really need to copy the data?
You could also link from Access to the SQL Server tables.

This way, you have tables in Access that look like normal local Access tables, but they are really just links to SQL Server tables. You can edit data in these tables in Access, but you are actually writing directly into the SQL Server database.

Here are some examples how to set this up:
Link to SQL Server data
Access to SQL Server: Linking Tables

Upvotes: 3

jjchiw
jjchiw

Reputation: 4425

As @Yuck said, you can not just copy the file and rename it, you need something like ETL or just a tool to export data.

I did xportdsl to copy from a h2database to a mssql and mssql to oracle http://code.google.com/p/xportdsl/

I used gorm and a hacky dsl that worked and it is still working

You can script a bat file to execute something like this java -jar xportdsl.jar test001.txt

Upvotes: 1

Yuck
Yuck

Reputation: 50825

MDB and MDF files are wildly different types. You can't just copy them.

You might try setting up an SSIS task to do a regular data transfer - something like ETL if you're familiar with that term.

EDIT: The reason you're seeing the file locked error is because SQL Server maintains that lock on the MDF file while the database is running. In order to move or copy it you need to take that particular database offline.

Upvotes: 1

Related Questions