Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to convert sql server express data to MS Access (.mdb) format Expand / Collapse
Author
Message
Posted Thursday, June 19, 2014 11:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 17, 2014 8:41 AM
Points: 9, Visits: 21
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
Post #1584002
Posted Thursday, June 19, 2014 12:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 13,328, Visits: 12,826
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)
Post #1584029
Posted Thursday, June 19, 2014 12:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 17, 2014 8:41 AM
Points: 9, Visits: 21
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?
Post #1584033
Posted Thursday, June 19, 2014 12:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 17, 2014 8:41 AM
Points: 9, Visits: 21
SSChampion, the users load the .mdb file into their local Access databases. What they do with it then is their bidness.
Post #1584035
Posted Thursday, June 19, 2014 12:52 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 13,328, Visits: 12,826
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)
Post #1584037
Posted Thursday, June 19, 2014 1:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 17, 2014 8:41 AM
Points: 9, Visits: 21
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.
Post #1584042
Posted Thursday, June 19, 2014 1:05 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 13,328, Visits: 12,826
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)
Post #1584044
Posted Friday, June 20, 2014 5:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 17, 2014 8:41 AM
Points: 9, Visits: 21
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.
Post #1584302
Posted Friday, June 20, 2014 7:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 7:29 AM
Points: 84, Visits: 1,046
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.
Post #1584349
Posted Friday, June 20, 2014 7:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 13,328, Visits: 12,826
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)
Post #1584352
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse