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,
GetDataTypeEnum(byval val,byval siz)
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,
sql ="SELECT name FROM sysobjects WHERE xtype='u' and status >0"
rs.Open sql, Cnn
if not rs.EOF then Redim arTables(0)
While not rs.eof
arTables(UBound(arTables)) = rs("name")
ReDim Preserve arTables(UBound(arTables) + 1)
ReDim Preserve arTables(UBound(arTables) - 1)
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)
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) &", "
tableCreate =tableCreate & rs.Fields.Item(rs.Fields.Count-1).Name &" "&_
conMdb.execute tableCreate, , 129
sqlMdb ="SELECT * FROM " & arTables(iCount)
rsMdb.Open sqlMdb, conMdb,2,3
while Not rs.EOF
For i=0 to rs.Fields.Count-1
if Not isnull(rs(i)) Then rsMdb(i)=rs(i)
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"
Case 7 GetDataTypeEnum= "DATETIME"
Case 11 GetDataTypeEnum= "YESNO"
Case 133 GetDataTypeEnum= "DATETIME"
if siz<255 then GetDataTypeEnum= "VARCHAR("& siz &")" else GetDataTypeEnum= "MEMO"
Case 201 GetDataTypeEnum= "MEMO DEFAULT ''"