Access 2003 to 2007 migration issues

[MrDeej]

Hello!

I have this code:

Sub oppdater_lokasjonsinfo()

Dim TempTbl As String
Dim TempQuery As String
Dim RstTil As New ADODB.Recordset
Dim qryDef As QueryDef
Dim Dbs As Database
Dim SqlString As String
Dim rs As Recordset
Dim MLGemba As Long
Dim MLMuda As Long
Dim MLJidoka As Long


Set Dbs = CurrentDb

TempTbl = "temptbl " & NtLogin & " lokasjonsinformasjon"
TempQuery = "tempqry " & NtLogin & " lokasjonsinformasjon"

If fTableExist(TempTbl) = False Then
    DoCmd.RunSQL "CREATE TABLE [" & TempTbl & "] (autonr COUNTER PRIMARY KEY, Lokasjon VARCHAR(50), [Loksum] VARCHAR(50), [Prodsum]varchar(50))"
Else
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * from [" & TempTbl & "]"
    DoCmd.SetWarnings True
End If

For Each q In Dbs.QueryDefs
    If q.Name = TempQuery Then
    Dbs.QueryDefs.Delete TempQuery
    End If
Next


    With RstTil
    .Open "SELECT * from [" & TempTbl & "]", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic

'cellelokasjoner
SqlString = "SELECT [LOK Loksummer].Lokasjon, [LOK Loksummer].[Antall tabletter] FROM [LOK Loksummer] WHERE ((([LOK Loksummer].Lokasjon) Not Like '*mdk*' And ([LOK Loksummer].Lokasjon) Not Like '*maskindiff*') AND (([LOK Loksummer].Varenr)=" & Me.tbx_varenr & ")) ORDER BY Right([lokasjon],4) DESC , [LOK Loksummer].Lokasjon"
Set qryDef = Dbs.CreateQueryDef(TempQuery, SqlString)
Set rs = Dbs.OpenRecordset(TempQuery)

    Do Until rs.EOF
    .AddNew
    !Lokasjon = rs!Lokasjon
    ![loksum] = rs![antall tabletter]
    .Update
    rs.MoveNext
    Loop
    .AddNew

DoCmd.DeleteObject acQuery, TempQuery

SqlString = "SELECT [LOK Loksummer].Lokasjon, [LOK Loksummer].[Antall tabletter] FROM [LOK Loksummer] WHERE ((([LOK Loksummer].Lokasjon) Like '*mdk*' And ([LOK Loksummer].Lokasjon) Not Like '*maskindiff*') AND (([LOK Loksummer].Varenr)=" & Me.tbx_varenr & ")) ORDER BY Right([lokasjon],4) DESC , [LOK Loksummer].Lokasjon"
Set qryDef = Dbs.CreateQueryDef(TempQuery, SqlString)
Set rs = CurrentDb.OpenRecordset(TempQuery)
    
    Do Until rs.EOF
        slashfrontpos = InStrRev(rs!Lokasjon, "a")
        Celle = Left(rs!Lokasjon, slashfrontpos)
        If Celle = "Gemba" Then
            MLGemba = MLGemba + rs![antall tabletter]
        ElseIf Celle = "Muda" Then
            MLMuda = MLMuda + rs![antall tabletter]
        ElseIf Celle = "Jidoka" Then
            MLJidoka = MLJidoka + rs![antall tabletter]
        End If
        rs.MoveNext
    Loop
    
    .AddNew
    !Lokasjon = "Gemba maskinlager"
    ![loksum] = MLGemba
    If Not IsNull(Me.tbx_gembaplanlagt) Then
    ![prodsum] = Me.tbx_gembaplanlagt
    End If
    .Update
    
    .AddNew
    !Lokasjon = "Muda Maskinlager"
    ![loksum] = MLMuda
    If Not IsNull(Me.tbx_mudaplanlagt) Then
    ![prodsum] = Me.tbx_mudaplanlagt
    End If
    .Update
    
    .AddNew
    !Lokasjon = "Jidoka Maskinlager"
    !loksum = MLJidoka
    If Not IsNull(Me.tbx_jidokaplanlagt) Then
    !prodsum = Me.tbx_jidokaplanlagt
    End If
    .Update
    

DoCmd.DeleteObject acQuery, TempQuery
    
    
    .Close
    End With
    
    
Set RstTil = Nothing
Set qryDef = Nothing
Set rs = Nothing


Me.Liste_over_lokasjonsantall.RowSource = "SELECT * from [" & TempTbl & "]"
End Sub

To do some work for me, this code works fine in Access 2003, but in Access 2007 SP1 it halts at line 56 with error message "Type Mismatch".

Anyone know of a solution to this?

[MrDeej]

I found the solotion my self.

I had to change line nr 9 to

Dim rs As DAO.Recordset

Now i have a big headache about the fsofilesearch which microsoft has removed from Access 2007. [Edit:******] ******


:P