Access 2007 to sql 2005

[access_developer]

Hello

I am trying to manipulate things in access vba

This is what i have
2 text files and sql link table ( odbc connected )

when i am trying to run the function its throwing me an error

"operation is not supported for this type of object"

Do i have to add the connection strings and change ado to any other object reference ??

I am newbie to sql so i am wondering what to do next

Any thoughts would be appreciated. Thank you

here is my code

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()) /* this was pointing to my old database which i thnk i need 2 chnge */
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