Click here to Skip to main content
12,399,799 members (46,132 online)
Click here to Skip to main content
Add your own
alternative version

Stats

106.3K views
2.4K downloads
31 bookmarked
Posted

Exporting Data from SQL to Access in Mdb File

, 14 Apr 2006 CPOL
Rate this:
Please Sign up or sign in to vote.
This Article will help you to export your data From Sql server to Access in form of Mdb file

Introduction

I was working on a Web Application in which client requested to keep backup of the Application DataBase in the Form of Access data in a MDB file for that i tried DTS package but the Database Server for application was kept on remote server where the host was not ready to provide rights to create DTS Package. so this particular code snippet was created.

There is basically two functions involved in this process,

  1. CreateBackup(byval db)
  2. GetDataTypeEnum(byval val,byval siz)

CreateBackup(byval db)

This function Accepts parameter db as the path of mdb file which has to be created as backup file. Here in order to create backup first we get all the User tables in Database then store these table names in array,

'Getting the name of all UserTables from SQL Server 
sql ="SELECT name FROM sysobjects WHERE xtype='u' and status >0"  
rs.Open sql, Cnn 'conMdbection to sql server
 if not rs.EOF then Redim arTables(0) 
   While not rs.eof 
     arTables(UBound(arTables)) = rs("name") 
    ReDim Preserve arTables(UBound(arTables) + 1) 
     rs.movenext 
   Wend 
  ReDim Preserve arTables(UBound(arTables) - 1) 
end if

and after that for each element(Table) in array create a table in mdb file then get structure of table in SQL Server then start Creating tables in mdb file for those tables only which have at least one row. then Copying of data from SQL server to Access table is done using two recordsets for Access and SQL respectively.

For iCount=0 To UBound(arTables) 'Creation of table in mdb file starts here 
 if rs.state=1 then rs.close 
  sql ="SELECT * FROM " & arTables(iCount) 
    rs.Open sql, Cnn 
      If Not rs.EOF Then 
        tableName = arTables(iCount) 
          tableCreate = "CREATE TABLE " & tableName & "(" 
             For i=0 to rs.Fields.Count-2 
               tableCreate =tableCreate & rs.Fields.Item(i).Name &" " &_
                GetDataTypeEnum(cint(rs.Fields.Item(i).Type), rs.Fields.Item(i).DefinedSize) &", " 
             Next 
               tableCreate =tableCreate & rs.Fields.Item(rs.Fields.Count-1).Name &" "&_
                GetDataTypeEnum(cint(rs.Fields.Item(rs.Fields.Count-1).Type),rs.Fields.Item(i).DefinedSize) &")"  
             conMdb.execute tableCreate, , 129 'Table Creation in Mdb 
    
      sqlMdb ="SELECT * FROM " & arTables(iCount) 'SQL to new Table in Mdb 
      rsMdb.Open sqlMdb, conMdb,2,3 
      while Not rs.EOF 
    rsMdb.addnew 'Adding Records to Table 
    For i=0 to rs.Fields.Count-1 
      if Not isnull(rs(i)) Then rsMdb(i)=rs(i) 'Copying Data From SQL table to Access Table 
    Next 
    rs.movenext 
    rsMdb.update 
   Wend 
    rsMdb.close 
 End If 
Next 

In above function while creating DDL(Data Defintion Language) Script to generate Access Table there was a problem that access the datatypes for sql and Access are different so to parse SQL datatypes into access i created Function GetDataTypeEnum(byval val,byval siz) which accepts two parameters one is the type of SQL field and other is its size,

GetDataTypeEnum(byval val, byval siz)

Case 3 GetDataTypeEnum= "INT"'"adInteger" 
Case 7 GetDataTypeEnum= "DATETIME"'"adDate" 
Case 11 GetDataTypeEnum= "YESNO"'"adBoolean"
Case 133 GetDataTypeEnum= "DATETIME"'"adDBDate"
Case 200 '"adVarChar" 
   if siz<255 then GetDataTypeEnum= "VARCHAR("& siz &")" else GetDataTypeEnum= "MEMO" 
Case 201 GetDataTypeEnum= "MEMO DEFAULT ''"'"adLongVarChar" 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Yashwant Shukla
Architect CSC
India India
No Biography provided

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 1 Pin
hernanliza7228-Feb-12 8:18
memberhernanliza7228-Feb-12 8:18 
GeneralMy vote of 2 Pin
vikas_badoni10-Oct-11 2:54
membervikas_badoni10-Oct-11 2:54 
Questiona .net version Pin
Mustafa Magdy25-Sep-11 10:25
memberMustafa Magdy25-Sep-11 10:25 
GeneralMy vote of 5 Pin
gvbadrinarayana7-Oct-10 1:46
membergvbadrinarayana7-Oct-10 1:46 
GeneralIts really nice Pin
ameermohid20-May-08 21:43
memberameermohid20-May-08 21:43 
Generalgreat start, but a number of errors Pin
waveform916-Mar-08 19:25
memberwaveform916-Mar-08 19:25 
GeneralGreat Work !! Pin
Nawaz Ijaz10-May-07 23:59
memberNawaz Ijaz10-May-07 23:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160721.1 | Last Updated 14 Apr 2006
Article Copyright 2006 by Yashwant Shukla
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid