Does my DAO code work with SQL BE (1 Viewer)

[scotthutchings]

I have a fairly large database application with hundreds of lines of DAO code such as:

Dim rs As Recordset
Dim rs1 As Recordset
Dim SQL As String
Dim OldValue As Variant
Dim NewValue As Variant

Project = Forms![Update Value]![ProjectName]
OldValue = Forms![Update Value]!OldReference
NewValue = Forms![Update Value]!ValueToUpdate
Set db = CurrentDb
Select Case (Me.CallingFieldName)
    Case "BidProposalReference"
        Set rs = db.OpenRecordset("SELECT * FROM [Bid - Proposal] " & _
            "WHERE ProjectName = """ & Project & """ AND " & _
            "ProposalReference = """ & OldValue & """")
        If Not rs.BOF Then
            rs.Edit
            rs!ProposalReference = NewValue
            rs.Update
            
            rs.Close
            Set rs = Nothing
        End If

Will this code work once I upsize the backend to Microsoft SQL Server Express 2008 R2?

I am in the process of trying to figure out how to upsize my database application. I have migrated the data using the Move Data menu option to SQL Server but as soon as I ran my first code, I got Error #91 (Database Object not set). This code worked great before the switch. I really don't understand how I convert my Access FE/BE database to an Access FE/Microsoft SQL BE application. I finally got Microsoft SQL Server installed on the Windows 2003 Server and have been able to connect to it, but I am stuck here. Can anyone point me in the right direction?

Thanks!
Scott

 

[Banana]

Yes, assuming the following:

1) You replace your old Access tables with linked SQL tables
2) The new linked SQL tables is renamed to be identical as their Access replacement.

It'll work because Access database engine is capable of working with any SQL backend (via ODBC) and thus will try to translate into ODBC SQL. However, you should take some caution because with linked tables, you can write query that would *seems* to work but perform badly because you forced Access to pull down to do local evaluation. You may find this section of Beginner's Guide to ODBC helpful.

 

[scotthutchings]

Does it matter that my table that was named ProjectFile in Access is now called dbo.ProjectFile in SQL?

 

[PeterOC]

When I upsized a db a couple of years back I had to change all the DAO code to ADO. But then I was keeping the FE as an adp (bad move). I found some code to remove all the dbo prefixes from every table.

 

[Banana]

Yes, you would want to remove "dbo_" that Access adds so you don't have to change your code. As PeterOC says, it's possible to do it via code. Something like this for example:

Public Sub StripPrefixFromTables()

Dim tdf As DAO.TableDef
Dim db As DAO.Database

Set db = CurrentDb

For Each tdf In db.TableDefs
   If Left$(tdf.Name) = "dbo_" Then
      tdf.Name = Mid$(tdf.Name, 5)
   End If
Next

End Sub

(untested aircod)

 

[scotthutchings]

Is this code that runs once and then the changes are permanent or does it run each time the database is open?

 

[Banana]

once, after you link a new SQL table.