VB Code to BackUp Data from SQL to Access

[manojkapparath]

Sir, I wish to know the Code in VB to Copy the data of all tables in the database of an SQL server to the database in ACCESS.The purpose is to keep a backup of data in another server .

[willakawill]

This question might be answered better in the Access or SQL Server forums. I have copied your post to both.

[ADezii]

Sir, I wish to know the Code in VB to Copy the data of all tables in the database of an SQL server to the database in ACCESS.The purpose is to keep a backup of data in another server .

You can accomplish this without a single line of code via Data Transformation Services (DTS). The following code will Export/Transform all the Tables in a SQL Server Database to an existing Access Database:
__1 Programs ==> SQL Server ==> Import and Export Data ==> DTS Import/Export Wizard ==> <next>
__2 Set DataSource = Mixcrosoft OLE Provider for SQL Server.
__3 Select Database whose Tables you wish to Export ==> <next>
__4 Set Destination = Microsoft Access.
__5 Select the Access Database to copy data to (*.mdb). Filename is required. If a UserName and Password is required for Database entry, supply these values.
__6 Select Copy table(s) and view(s) from the source database. <next>
__7 Click Select all Tables and finish the Export/Transformation. All the data in your SQL Tables now reside in your pre-defined Access Database.
__8 I am by no means an expert in this area, but I think you can encapsulate all your Export setting in something called a DTS Package and basically automate the entire process. This question is more suited for the SQL Server Forum.
__9 Hope this helps and good luck.

[iburyak]

I can give you an instruction on how to create and schedule DTS job but I want to be sure it is a way you want to go with.
I don't want to waist my time on complicated instructions that are not going to be used at the end.

To do import from VB just create pass-through query to SQL server and then create another query that inserts result of a pass-through query to Access table.
From VB just clear or drop existing tables then execute insert query.

Good Luck.