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

  • barry 50045

    SSC-Addicted

    Points: 499

    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

    SSC Guru

    Points: 286408

    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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • barry 50045

    SSC-Addicted

    Points: 499

    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

    SSC-Addicted

    Points: 499

    SSChampion, the users load the .mdb file into their local Access databases. What they do with it then is their bidness.

  • Sean Lange

    SSC Guru

    Points: 286408

    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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • barry 50045

    SSC-Addicted

    Points: 499

    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

    SSC Guru

    Points: 286408

    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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • barry 50045

    SSC-Addicted

    Points: 499

    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

    SSCommitted

    Points: 1505

    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

    SSC Guru

    Points: 286408

    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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • barry 50045

    SSC-Addicted

    Points: 499

    Thank you for the very detailed example. Wow!

  • barry 50045

    SSC-Addicted

    Points: 499

    Thanks for all of the help. That makes much more sense to me.

  • rf44

    SSCommitted

    Points: 1505

    You're welcome!

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply