June 19, 2014 at 11:58 am
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
June 19, 2014 at 12:42 pm
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/
June 19, 2014 at 12:49 pm
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?
June 19, 2014 at 12:50 pm
SSChampion, the users load the .mdb file into their local Access databases. What they do with it then is their bidness.
June 19, 2014 at 12:52 pm
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/
June 19, 2014 at 1:02 pm
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.
June 19, 2014 at 1:05 pm
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/
June 20, 2014 at 5:28 am
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.
June 20, 2014 at 7:30 am
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.
June 20, 2014 at 7:30 am
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/
June 20, 2014 at 11:44 am
Thank you for the very detailed example. Wow!
June 20, 2014 at 11:45 am
Thanks for all of the help. That makes much more sense to me.
June 20, 2014 at 1:41 pm
You're welcome!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply