Move to SQL Server with no programming changes in Access

[BallunarCrew]

I am trying to move my Access 2007 database to a SQL Server backend without having to make all the programming changes in the Access front end database for things such as changing "No" criteria to "False" or a numerical value since SQL Server does not have a Yes/No data type. I have a co-worker that says he has seen it done by using an intermediate database of linked tables or something similar. The idea is to have an Access database that contains the links to the SQL Server tables and another Access database that links to the links only database. The problem is that I can't create a linked table in the front end to another database that only has links in it; no tables show up in the items to link to. Does anyone have an idea?

 

[dhookom]

Access doesn't store "No" or "False" in Yes/No fields. It stores 0 (or -1 for Yes). I expect your queries will still work with the linked tables since they can translated.

SQL Server does have a bit field that stores 0, 1, or Null. If you have yes/no fields, you should either change them in your SQL Server database to INT or at least set a default value. If you don't, you may have issues updating your tables.

Duane
Hook'D on Access
MS Access MVP

 

[BallunarCrew]

Thanks Duane. I was just using that as an example. Another example is that SQL Server does not understand date literals with # signs around the date. I have several places in my code where I set a sub-form to a filtered set of data based on a date entered by the users. The code looks like this:

Dim filterStr As String
If Not (IsNull(Me!txtFilterDate)) Then
filterStr = "DtToC = #" & Me!txtFilterDate.Value & "#"
With Me.subAppsFromOutlook.Form
.Filter = ""
.Filter = filterStr
.FilterOn = True
End With
End If
As I understand it, this will have to be changed to use quote marks rather than the pound signs. The document SSMAAcess.docx at this link talks about all these programming changes to look out for.

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=8775

We came up with an idea to create links to the SQL Server tables and then create a select query in Access against each linked table. If we use those queries as the record source for our forms, report, etc, maybe it will let us leave all the programming alone but I don't know yet. I also don't know what the performance hit will be.

 

[dhookom]

Have you tried this? Again, I believe Access does all the translation for you. If you create ADO recordsets, there might be some issues.

Duane
Hook'D on Access
MS Access MVP

 

[BallunarCrew]

I will try it when I get SQL Server all setup and configured. We have not used ADO recordsets but have used DAO recordsets extensively.