UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Access to SQL Server    
 
   
nickusaf
post Nov 7 2007, 08:12 AM
Post#1



Posts: 595
Joined: 23-October 02
From: Dayton, OH


I have an Access DB that has outgrown itself and now more people need to get the data...so we are planning on moving my tables into the company SQL Server...but I still want to use my Program as the Front End. What type of planning should I do, and what problems can I expect to encounter?
Go to the top of the page
 
datAdrenaline
post Nov 7 2007, 09:38 AM
Post#2


UtterAccess Editor
Posts: 17,930
Joined: 4-December 03
From: Northern Virginia, USA


If you use linked tables in your front end, your app pretty much can stay the same as it was when your data was in JET format.

Somethings to keep in mind:
* The BIT datatype in SQL Server comes through ODBC as a Boolean, which is good, however, you MUST declare the BIT field to NOT ALLOW NULLS, plus I set the default to 0. ODBC does not know how to handle a NULL BIT. Also, a BIT field uses a value of 1 for not false and 0 for false.

* Access will convert the SQL Server datatype of BIGINT to a TEXT field. Also, if you use a BIGINT as an Autoincrementing PK in the SQL Server table, your Access front end will visually display that all your records are deleted .... so .... I recommend to use the BIGINT datatype sparingly, if at all, for Access FE's to SQL Server

* As you may or may not know, dates are stored as numbers in JET and SQL Server. The base date (the numeric value of 0) in SQL Server is 1/1/1900 12:00:00 AM, where as in JET its 12/30/1899 12:00 AM. ODBC handles this fairly well but if you start connecting to the SQL Server tables directly you might get some "Off By 2" errors ...now you will know why!

* If you do use PASSTHRU queries, or use a direct connection object to the SQL Server, you need to remember to use a laguage and function syntax SQL Server understands.... The date delimiter is one of those things that get people. The date delimiter is # in MS Access, In SQL Server its ' ... so ... #12/31/2005# in MsAccess would be '12/30/2005' in SQL Server.

* Also some common function equivalents (For use in PT's and direct SQL Server connections):
Jet -> SQL Server
---------------------
Now() -> GetDate()
Nz() -> ISNULL() .... (NOTE: ISNULL() in SQL Server is NOT the same as IsNull() in VBA/Jet)
IIF() -> CASE WHEN END

Check out: T-SQL Functions

* Again, with PT's and direct SQL Server connections, Wildcard characters ... the * used in in MSAccess would be % in SQL Server ... so ...
MSAccess: SELECT * FROM tblTable WHERE MyName Like 'B*'
SQL Server: SELECT * FROM tblTable WHERE MyName Like B%

In addition passthrough will NOT except CONTROL REFERENCES ...

Example:

Valid Syntax In MSAccess:
SELECT * FROM tblMyTable WHERE CustomerID = [Forms]![frmMyForm]![CustomerID]

But if you send that statement in a PASSTHRU, the SQL Server has NO IDEA what [Forms]![frmMyForm]![CustomerID] is! ... so you have to modify the passthru's queries SQL property to get the correct info back from the passthru ...

CODE
Dim strSQL As String
trSQL = "SELECT * FROM tblMyTable WHERE CustomerID = " & Forms("frmMyForm")!CustomerID
CurrentDB.QueryDefs("selMyPassthru").SQL = strSQL
Docmd.openquery "selMyPassthru"


* In PT's and direct Connections, SQL Server does NOT understand the keyword "True" or "False" ... in PT's I highly encourage you to test for <> 0 for NOT False (Truth) and = 0 for False:

SELECT * FROM tblMyTable WHERE someBITfield <> 0

Check out this lengthy discussion on True and False in general
....

I have some more posts out there on UA on this topic, but this should get you started!
Go to the top of the page
 
datAdrenaline
post Nov 7 2007, 10:21 AM
Post#3


UtterAccess Editor
Posts: 17,930
Joined: 4-December 03
From: Northern Virginia, USA


Here are a couple of other threads that may help you out:
Access/JET to SQL Server
Bit Field Discussion
Go to the top of the page
 
nickusaf
post Nov 7 2007, 10:36 AM
Post#4



Posts: 595
Joined: 23-October 02
From: Dayton, OH


Thank you for taking the time to write all this down. This is exactly what I was looking for... the common things everybody bangs their head on when doing this type of conversion!!
Thanks!!!
Go to the top of the page
 
datAdrenaline
post Nov 7 2007, 12:05 PM
Post#5


UtterAccess Editor
Posts: 17,930
Joined: 4-December 03
From: Northern Virginia, USA


Your welcome!! ...
..
Another thing I thought of ... data RETURNED by a passthroug query is NOT editable, so I PT's a LOT for reporting and browsing records, but then use linked tables to edit data... But these kinds of things are usually done in progression:
First Stage: Get your app working. Move your tables to SQL Server and Link them, fix any issues, which there should not be any ... the task is to get your tables and data to SQL Server, note the I personally DO NOT use the wizard, it like to do things on my own to. Click Here for a post on moving your tables to SQL Server ...
Second Stage: Experiment with passthroughs as your recordsource for reports, as well as to browse/search for records if you have forms that search for data
Third Stage (and beyond): When working with data in your code, use an ADO connection that connects directly to your SQL Server, thus your code will be issuing a direct connection to SQL Server.
...
Moving your data to SQL server will generally expand ones programming ability if you truly exploit the capabilities of SQL Server. But many folks are happy to just stick with completion of the First Stage.
Good Luck!
Go to the top of the page
 
alexdyb
post Nov 8 2007, 12:59 AM
Post#6



Posts: 19
Joined: 7-January 06
From: Russia


Hi,
one more thing to add. You can use your existing forms bound to linked tables, but it is quite important to show only filtered data. For example - if you open Orders form (like in northwind DB) with all records - then you can easy get dead locks on sql server. Instead of this you need to make a new form where user can located required order and then open Orders form with only one that order record.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 10:23 PM