Declare Sql server location in access vba code

[access_developer]

Hello

I have recently migrated my access database to SQL server 2005 as back end database

Earlier, the database was connected using

Set db = CurrentDb
Set dbDst = DBEngine(0).OpenDatabase(GetDataFullPath())

were getdatafullpath = network location declared in vba constants.

Now my concern is

Is it possible to declare the sql server location in vba constants as i wont have to make any changes in vba code.

I have created ODBC connection and linked all the tables successfully now if i am trying to edit/update/delete anything from the access front end
i am getting the following error

"Operation is not supported for this type of object"

or do i have to call the sql odbc driver in each and every function call independently.

any thoughts will be appreciated. Thank you

VBA Code which i trying to run

Private Function UpdateMarketData() As Boolean
On Error GoTo EH_UpdateMarketData
Call LogError(0, "", "*** Updating Market Data Started", True)
Dim db As DAO.Database
Dim dbDst As DAO.Database
Dim rsSrc As DAO.Recordset
Dim rsDst As DAO.Recordset
Dim sSQL As String
Dim lMarketID As Long
Dim bUpdate As Boolean
Dim bAdd As Boolean
Dim bEdit As Boolean
Dim bHasError As Boolean
Dim lCount As Long
Dim lAddCount As Long
Dim lEditCount As Long
Dim lErrorCount As Long

Set db = CurrentDb
Set dbDst = DBEngine(0).OpenDatabase(GetDataFullPath())
sSQL = "SELECT Market_Text.*, Employee_Text.EmployeeID AS GPID " & _
"FROM Market_Text LEFT JOIN Employee_Text ON Market_Text.MarketID = Employee_Text.MarketID " & _
"WHERE Employee_Text.PositionCodeID=" & enPositionCode.GP & " AND Employee_Text.TermDate=0;"
Set rsSrc = db.OpenRecordset(sSQL)
Set rsDst = dbDst.OpenRecordset("Market")
rsDst.Index = "MarketID"
Do Until rsSrc.EOF
DoEvents
lCount = lCount + 1
bUpdate = False
bAdd = False
bEdit = False
bHasError = False
lMarketID = rsSrc!MarketID
rsDst.Seek "=", lMarketID
If rsDst.NoMatch Then
rsDst.AddNew
bUpdate = True
bAdd = True
rsDst!MarketID = lMarketID
Else
rsDst.Edit
bEdit = True
End If

If Nz(rsDst!MGLongName, "") <> Nz(rsSrc!MarketLongName, "") Then
rsDst!MGLongName = rsSrc!MarketLongName
bUpdate = True
End If

If Nz(rsDst!MGShortName, "") <> Nz(rsSrc!MarketShortName, "") Then
rsDst!MGShortName = rsSrc!MarketShortName
bUpdate = True
End If

If Nz(rsDst!GPID, "") <> Nz(rsSrc!GPID, "") Then
rsDst!GPID = rsSrc!GPID
bUpdate = True
End If

If bUpdate Then
rsDst.Update
If bHasError Then
lErrorCount = lErrorCount + 1
Else
If bAdd Then
lAddCount = lAddCount + 1
End If
If bEdit Then
lEditCount = lEditCount + 1
End If
End If
End If

rsSrc.MoveNext
Loop

Call LogError(0, "", "*** Updating Market Data Completed - Total: " & lCount & " Added: " & lAddCount & " - Updated: " & lEditCount & " - Errors: " & lErrorCount, True)
UpdateMarketData = True
Exit_UpdateMarketData:
db.Close
Set db = Nothing
dbDst.Close
Set dbDst = Nothing
Exit Function
EH_UpdateMarketData:
bHasError = True
Call LogError(err, Error & " MarketID: " & lMarketID, "", True)
Resume Next
End Function