From Access to SQL Server
-
Hey People,
I'm trying to go from Access backend to a SQL backend..
Now my question: Where to begin????
I wanna get the best I can out of SQL server so I want a good start you know..
There r many sites out there providing lots and lots of info, but Im lost here..
please advise
Thanks in advance,
Richard
-
Theoretically you could simply run the Access upsizing wizard http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part2/c0561.mspx
I would start by reading up about the different datatypes in SQL Server and mapping them to the various MS Access types.
In Access you have a maximum text field size of 255 characters. In SQL the equivalent is VARCHAR with a maximum of 8000 characters.
This means that you may have some scope to reduce the number of access Memo fields.
I would make a list of all the queries that you run against your access database.
Those that carry out actions or those that require parameters for a select statement can be classed as stored procedures.
Those that do selects with no parameters will either map to SQL Server views or stored procedures.
The biggest difference between the SQL within MS Access and SQL Server is in the UPDATE statement.
-
Hey David,
Thanks allot.
I got an ODBC connection to the SQL server and linked my tables using the ODBC connection.. is this the best I can do with SQL ?
Also, I'm unable to Add records to a form based on a SELECT statement as source... when I open the table I can Add.. Im possitive that I have a Unique key in each table.. Any suggestions?
Thanks,
Richard
-
If your SELECT involves a single table then you should be able to Add.
Make sure that whatever user you are connecting to the database has read/write access.
Your best bet is to set up a role and grant the necessary privileges then attach users to that role.
Access causes locking problems with SQL Server so if you allow Access users to enter data via a data sheet view you may have problems.
I found that I had to use a form and then programatically taken the values of the fields and pass them into a stored procedure via an Access passthru query.
I later switched to using VB and ADO Command objects ditching Access altogether.
The shift from Access to VB was a bit traumatic. Access has so many great features that VB seemed to be a step backwards.
I still have users who need to use Access but the approach I take is to have SQL Server download the relevant tables to Access via a DTS package.
Users can then download the resulting MDB file and play around to their hearts content without affecting my server.
-
Hey Again David,
quote;
"...I found that I had to use a form and then programatically taken the values of the fields and pass them into a stored procedure via an Access passthru query..."
That sounds as alot of abra-ca-dabra for me
Can u explain that more or got some good links on Stored Procedures? I just use query's now..
Rgds,
Richard
-
I started off by linking an Access frontend to an SQL Server backend but found that performance while reasonable on our LAN was not very good on the WAN.
Ditched that option and then went for an unbound frontend. Loading and saving all done in VBA. Have 400 plus registered users and the server barely ticking over.
I started off with SAMS "Microsoft Access Developer's Guide to SQL Server" authors: Mary Chipman and Andy Baron.
-
Hey Stefan,
Thanks for your reply.
I have that book already 🙂
Do you perhaps can send me a sample of your database ?(offcourse I'll be very discreet with the information)
Thanks in advance,
Richard
-
I don't think it would help you. It is very large and has lots and lots of very specific funtionanility. You are far better off working through the examples in the book, I based a lot of what I did on those examples.
-
Ok, Will do that..
Thanks
Do I understand correctly that you don't have ANY table in your frontend? and did u used an ADP as frontend?
Also, Do U use a DSN-less connection (If so can you please tell me how) or do u use a DSN (ODBC) connection ?
Sorry for all the uqestions, but It would be very helpfull
Thanks,
Richard
-
I am using Access 97 as the frontend so do not use ADP, I use a standard mdb file (actually an MDE for users)
The only tables I have in the frontend are:
1. Reference data tables - these are only downloaded on connection if changed (a table stores a flag for each reference table which is modified by trigger)
2. Temporary tables. Most of the time I use ADO or Passthru queries but occasionaly this does not work e.g. exporting to Word so I create a temporary table.
I based the method I use to connect on Chapter 11 - Scaling up with unbound access applications.
Off the top of my head it works like this:
The application opens with a login form. This login form stores the string for both ODBC and OLE DB connections. ODBC is used for passthru queries (when you need multiple rows), OLEDB is used for ADO. I do not use a DSN.
Installation on a user machine is just question of making sure that the MDAC drivers are up to date and downloading the frontend.
When the user inputs his user id and password (not required with NT authentication) and clicks OK, the login form is made invisible but the two connection strings are available to the app.
Each time access needs to connect to the backend run the sample code e.g. for passthru:
Private Sub Report_Open(Cancel As Integer)
Dim strSql As String
On Error GoTo errorHandling
DoCmd.Maximize
strSql = "procStatsTradeTotals"
PassThroughFixup "qryDummyPassthru", strSql, StrConnect:=Forms!frmlogin.ODBCConnect
RecordSource = "qryDummyPassthru"
ExitHere:
Exit Sub
errorHandling:
Select Case Err.Number
Case Else
MsgBox Err.Number & Err.Description
End Select
End Sub
Tip, I originally created a query for each separate passthru but you can actually use a single dummy passthru for every passthru query
Public Sub PassThroughFixup( _
strQdfName As String, _
Optional strSql As String, _
Optional StrConnect As String, _
Optional fRetRecords As Boolean = True)
' Modifies pass-through query properties
' Inputs:
' strQdfName: Name of the query
' strSQL: Optional new SQL string
' strConnect: Optional new connect string
' fRetRecords: Optional setting for ReturnsRecords--
' defaults to True (Yes)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs(strQdfName)
If Len(strSql) > 0 Then
qdf.SQL = strSql
End If
If Len(StrConnect) > 0 Then
qdf.Connect = StrConnect
End If
qdf.ReturnsRecords = fRetRecords
qdf.Close
Set qdf = Nothing
End Sub
or for ADO
Public Sub sPopulateCompaniesMain()
Dim rst As ADODB.Recordset
Dim fOK As Boolean
Dim strSql As String
Dim strMsg As String
On Error GoTo HandleErr
fOK = OpenConnection()
If fOK = False Then
MsgBox "Unable to Connect", , _
"Can't connect to SQL Server"
Forms!frmlogin.Visible = True
GoTo ExitHere
End If
' Get the Company record
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Open Source:="EXEC procCustomerMainSelect2 " & _
OrganisationID, _
ActiveConnection:=gcnn
' Display Company data in form controls
If rst.EOF Then
MsgBox "Record can't be found.", , _
"Company does not exist"
GoTo ExitHere
Else
'Populate main form fields
With rst
OrganisationName = !OrganisationName
Address = !Address
End With
ExitHere:
Exit Sub
HandleErr:
Select Case Err
Case -2147467259
'MsgBox "Connection error, please try again", vbCritical, "Connection failure"
'
If gcnn.State <> 0 Then
gcnn.Close
sPopulateCompaniesMain
Else
MsgBox "Connection error, please login again", vbCritical, "Connection error"
End If
Case Else
MsgBox Err & ": " & Err.Description, , "cmdLoad_Click() Error"
End Select
Resume ExitHere
Resume
End Sub
Public gcnn As ADODB.Connection
Public Function OpenConnection() As Boolean
' Opens Global ADO Connection,
' if it isn't already open.
' Returns True if connected.
' Passes any errors back to the procedure
' that called this one.
On Error GoTo HandleError
Dim boolState As Boolean
If gcnn Is Nothing Then
Set gcnn = New ADODB.Connection
End If
If gcnn.State = adStateOpen Then
boolState = True
Else
If Not IsFormOpen("frmLogin") Then
DoCmd.OpenForm "frmLogin", WindowMode:=acDialog
End If
gcnn.ConnectionString = Forms!frmlogin.OLEDBConnect
gcnn.Open
If gcnn.State = adStateOpen Then
boolState = True
Else
boolState = False
End If
End If
OpenConnection = boolState
ExitHere:
Exit Function
HandleError:
OpenConnection = False
Err.Raise Err.Number, Err.Source, _
Err.Description, Err.HelpFile, Err.HelpContext
Resume ExitHere
End Function
-
As far as moving tables and data over, why don't you just use SQL Server's DTS? As part of my job, I often have to convert Access databases into either SQL Server or Oracle. DTS is great for this. Another tool I use is Borland's Datapump, but it only works with Access '97.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply