Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to convert sql server express data to MS Access (.mdb) format


How to convert sql server express data to MS Access (.mdb) format

Author
Message
barry 50045
barry 50045
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 23
Anybody,
Does anyone know of a way that I can pull all of the data from all of my tables in my Sql Server Express 2008 database and convert it to a .mdb format? My company is taking over the support of a web application and moving it to a new server. The new server has a Sql Server Express instance running on it. The application is currently hosted at a location that has a full-blown version of Sql Server and accomplishes this task via SSIS. Currently, the user of the application can click a link on the website that allows them to download a zipped .mdb file onto their local machines.

Does anyone know of a way to do this without using SSIS? Please. I am at witts-end.

Thanks,
Barry
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16580 Visits: 17023
barry 50045 (6/19/2014)
Anybody,
Does anyone know of a way that I can pull all of the data from all of my tables in my Sql Server Express 2008 database and convert it to a .mdb format? My company is taking over the support of a web application and moving it to a new server. The new server has a Sql Server Express instance running on it. The application is currently hosted at a location that has a full-blown version of Sql Server and accomplishes this task via SSIS. Currently, the user of the application can click a link on the website that allows them to download a zipped .mdb file onto their local machines.

Does anyone know of a way to do this without using SSIS? Please. I am at witts-end.

Thanks,
Barry


Eek your website allows users to copy the entire database? What purpose would this serve? You would have to get knee deep in the code here since you will have to do everything manually. Basically you will have to create an access database and then create and populate whatever tables you need via t-sql.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
barry 50045
barry 50045
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 23
The website allows the user to download a zipped .mdb file that was created in ssis and another program that zips it I guess. The .mdb file is all of our sql tables and data converted to .mdb. I need to know of a way to create this .mdb file without using ssis. Do you know how to do that?
barry 50045
barry 50045
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 23
SSChampion, the users load the .mdb file into their local Access databases. What they do with it then is their bidness.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16580 Visits: 17023
barry 50045 (6/19/2014)
I need to know of a way to create this .mdb file without using ssis. Do you know how to do that?


As I said before.


Basically you will have to create an access database and then create and populate whatever tables you need via t-sql.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
barry 50045
barry 50045
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 23
You're not saying insert the data from one sql table directly into its corresponding access table via t-sql....are you? If so, I didn't know you could do that.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16580 Visits: 17023
barry 50045 (6/19/2014)
You're not saying insert the data from one sql table directly into its corresponding access table via t-sql....are you? If so, I didn't know you could do that.


That is the only way I know you could do this. I don't know what the current SSIS package does but I suspect it creates an Access database and then establishes a connection to it. Finally it transfers all the data. You would need to do something similar but using application code on your webserver.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
barry 50045
barry 50045
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 23
using t-sql, where and how would the connection to the access db be? Is there any way you could give me an example? Please believe me, I'm not asking you to spent a bunch of time to create an elaborate example just a simple one to get me started. As I said before, I had no idea you could do this in via t-sql.
rf44
rf44
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 1088
Here's a solution you can use provided that:
1. MS Access is installed on the SQL Server machine.
2. You can create a text file and write into it from your SQL Server.
3. You can start MS Access from your SQL Server.

Step 1:
a) Create an Access database and name it ImportFromSQLServer.mdb or ImportFromSQLServer.accdb
b) In this database, create a Standard Module and paste this code into it:

Private m_strConnection As String
Private m_strDbName As String

Private Sub CreateDatabase()

If Len(Dir(m_strDbName)) > 0 Then Kill m_strDbName
Application.DBEngine.CreateDatabase m_strDbName, dbLangGeneral

End Sub

Private Function GetTableList() As Variant

Const c_SQL As String = "SELECT name FROM sys.objects WHERE type = 'U';"

Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim var As Variant

Set qdf = CurrentDb.CreateQueryDef("")
With qdf
.Connect = m_strConnection
.SQL = c_SQL
Set rst = .OpenRecordset
With rst
If Not .EOF Then
.MoveLast
ReDim var(0 To .RecordCount - 1)
.MoveFirst
Do While Not .EOF
var(.AbsolutePosition) = .Fields(0).Value
.MoveNext
Loop
End If
.Close
End With
.Close
End With
Set rst = Nothing
Set qdf = Nothing
GetTableList = var

End Function

Private Sub ImportTable(ByVal TableName As String)

Const c_SQL1 As String = "SELECT * FROM @T;"
Const c_SQL2 As String = "SELECT * INTO @T IN '@D' FROM qryImport;"

Dim qry As DAO.QueryDef

If DCount("*", "MSysObjects", "name='qryImport'") > 0 Then DoCmd.DeleteObject acQuery, "qryImport"
Set qry = CurrentDb.CreateQueryDef("qryImport")
With qry
.Connect = m_strConnection
.SQL = Replace(c_SQL1, "@T", TableName)
End With
CurrentDb.Execute Replace(Replace(c_SQL2, "@T", TableName), "@D", m_strDbName), dbFailOnError
DoCmd.DeleteObject acQuery, "qryImport"

End Sub

Public Function StartUp()

Dim var As Variant
Dim str As String
Dim intHandle As Integer
Dim i As Long

var = Split(Command$)
For i = 0 To UBound(var)
If Left(var(i), 5) = "/INI:" Then
str = Trim(Mid(var(i), 6))
Exit For
End If
Next i
If Len(Dir(str)) = 0 Or Len(str) = 0 Then str = Replace(CurrentDb.Name, ".mdb", ".ini")
intHandle = FreeFile
Open str For Input As #intHandle
Do Until EOF(intHandle)
Line Input #intHandle, str
Select Case Left(str, 5)
Case "/DBN:": m_strDbName = Trim(Mid(str, 6))
Case "/CNN:": m_strConnection = Trim(Mid(str, 6))
End Select
Loop
Close #intHandle
var = GetTableList
CreateDatabase
For i = 0 To UBound(var)
ImportTable var(i)
Next i
Application.Quit

End Function


c) Create a Macro:
- Action: RunCode
- Function Name: StartUp()
- Name: AutoExec
d) Compile, save and close the Access database.

Step 2:
Create a stored procedure that can create a text file:
a) By default, this text file will be named ImportFromSQLServer.ini and will be located in the same folder as the database you created in step 1a.
b) Write two lines in the file ImportFromSQLServer.ini:
- One line begins with /CNN: followed by the connection string (e.g. /CNN:ODBC;DRIVER={SQL Server};SERVER=ISKENDER;DATABASE=Sales;Trusted_Connection=Yes;).
- A second line begins with /DBN: followed by the full path to the Access database where you want to export the tables (e.g. /DBN:U:\Access\Sales.mdb).

Step 3:
Start Access from a stored procedure, passing the full path to the Access database you created in step 1a.
Note: You can also create a batch file to start Access and have this batch file execute from the SQL Server.
Example (form Access 11 (MS Office 2003):
- File name: StartAccess.cmd
- File contents: "C:\Program Files\Microsoft Office\OFFICE11\msaccess.exe" "U:\Access\ImportFromSQLServer\ImportFromSQLServer.mdb"

Notes:
1) If you want to specify another .ini file than the default one, add the parameter /INI: followed by the full path to the .ini file on the command line starting Access.
2) You'll need to refine the process if you want to import the indexes defined on the SQL Server tables.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16580 Visits: 17023
barry 50045 (6/20/2014)
using t-sql, where and how would the connection to the access db be? Is there any way you could give me an example? Please believe me, I'm not asking you to spent a bunch of time to create an elaborate example just a simple one to get me started. As I said before, I had no idea you could do this in via t-sql.


In your web application you would have a database connection to your sql server AND a database connection to your Access database. Then you need to write create table statements and execute them against your Access connection. Then you write your insert statements the same way. It is not going to be pretty and will take a mountain of code but you can do it. You have to remember that Access is just a DBMS and you can write create table scripts and insert statements nearly identical to sql server syntax.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search