Link to home
Start Free TrialLog in
Avatar of Karen Schaefer
Karen SchaeferFlag for United States of America

asked on

Problem with Type mismatach - Open Recordset

I am getting type mismatch error on the following code- what am I doing wrong.

Thanks,

Karen

Function Get_Latest_Metrica_Data_From_MTR2()
On Error GoTo Get_Latest_Metrica_Data_From_MTR2_Err
Dim nTable As String
Dim rs As dao.Recordset
Dim currentdb As Database
Dim strSql As String

'SetWarnings = False

strSql = "SELECT Name, Type" & _
        " FROM MSysObjects" & _
        " WHERE (((Name) Not Like " * M * ") AND ((Type)=1));"
Set rs = currentdb.OpenRecordset(strSql)


fOSUserName
Err = 0
If rs.RecordCount > 0 Then
    If Err <> 0 Then
        nTable = rs.Name
        DoCmd.DeleteObject acTable, " & chr(34) & nTable & chr(34) & "
        DoCmd.TransferDatabase acImport, "ODBC", "ODBC;DSN=MTR2;UID=" & Chr(34) & fOSUserName & Chr(34) & ";DBQ=MTR2 ;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;;TABLE=" & Chr(34) & nTable & Chr(34) & ", acTable, " & Chr(34) & nTable & Chr(34) & "," & Chr(34) & nTable & Chr(34) & ", False"
    Else
        Exit Function
    End If
End If

Get_Latest_Metrica_Data_From_MTR2_Exit:
    Exit Function

Get_Latest_Metrica_Data_From_MTR2_Err:
    MsgBox Error$
    Resume Get_Latest_Metrica_Data_From_MTR2_Exit

End Function
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

please try this:
strSql = "SELECT Name, Type" & _
        " FROM MSysObjects" & _
        " WHERE (((Name) Not Like ""*M*"") AND ((Type)=1));"
what is this line

DoCmd.DeleteObject acTable, " & chr(34) & nTable & chr(34) & "


Should it not just be

DoCmd.DeleteObject acTable, nTable


Avatar of Karen Schaefer

ASKER

Now I get object varialble or with block not set.
strSql = "SELECT Name, Type" & _
        " FROM MSysObjects" & _
        " WHERE (((Name) Not Like '*M*') AND ((Type)=1));"
>Now I get object varialble or with block not set.
on which line?

also, what is the following procedure doing (maybe the error occurs in there)
fOSUserName
Thanks tried that still getting the object variable error.

Thanks,

Karen
Function Get_Latest_Metrica_Data_From_MTR2()
On Error GoTo Get_Latest_Metrica_Data_From_MTR2_Err
Dim nTable As String
Dim rs As DAO.Recordset
Dim currentdb As Database
Dim strSql As String

'SetWarnings = False
'strSql = "SELECT Name, Type" & _
        " FROM MSysObjects" & _
        " WHERE (((Name) Not Like ""*M*"") AND ((Type)=1));"
strSql = "SELECT Name, Type" & _
        " FROM MSysObjects" & _
        " WHERE (((Name) Not Like '*M*') AND ((Type)=1));"
Set rs = currentdb.OpenRecordset(strSql)
Debug.Print strSql


fOSUserName
Err = 0
If rs.RecordCount > 0 Then
    If Err <> 0 Then
        nTable = rs.Name
        DoCmd.DeleteObject acTable, nTable
       'DoCmd.DeleteObject acTable, " & chr(34) & nTable & chr(34) & "
        DoCmd.TransferDatabase acImport, "ODBC", "ODBC;DSN=MTR2;UID=" & Chr(34) & fOSUserName & Chr(34) & ";DBQ=MTR2 ;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;;TABLE=" & Chr(34) & nTable & Chr(34) & ", acTable, " & Chr(34) & nTable & Chr(34) & "," & Chr(34) & nTable & Chr(34) & ", False"
    Else
        Exit Function
    End If
End If

Get_Latest_Metrica_Data_From_MTR2_Exit:
    Exit Function

Get_Latest_Metrica_Data_From_MTR2_Err:
    MsgBox Error$
    Resume Get_Latest_Metrica_Data_From_MTR2_Exit

End Function
this fOSUserName is function to get current user id from api call.

K
It is dying on the
Set rs = currentdb.OpenRecordset(strSql) portion of the code.

k
which line?
currentdb is a reserved word is it not

u are defining it

get rid of this, what happens

Dim currentdb As Database

>It is dying on the
>Set rs = currentdb.OpenRecordset(strSql) portion of the code.

this means that currentdb is not set.

do you use a Access Database Project by any chance (and not a "regular" access database)?
changed it to curdb as database with same results - still get error.
K
I see rockiroads already found the main problem...
it shoould be something like:

Dim DB As Database
Dim RS As Recordset
Dim strSql As String

strSql = "SELECT Name, Type" & _
        " FROM MSysObjects" & _
        " WHERE (((Name) Not Like '*M*') AND ((Type)=1));"

Set DB = CurrentDb()                'Use the current database
Set RS = DB.OpenRecordset(strSql)      'actually open the recordset
regular access what is the proper syntax for declaring the current database and for passing the sql statement as the recordset.

Thanks,

Karen
What I usually do is this

set rs = currentdb.OpenRecordset ....


if u notice, in your code, u are doing it correctly

Set rs = currentdb.OpenRecordset(strSql)


All u have to try is removing the definition
Dim currendb

and nothing else

OK Thanks to everyone for the suggestion - however, Now that I have this portiion of the code working I realize that there is a flaw in my approach.

I have list of tables that need to be replaced on a weekly basis.  These tables are Oracle tables and I do not have access to the Oracle database to manipluate it.  So my solution is to replace the current tables in my access database with the update of the oracle tables via the ODBC connection.

I know I need to modify my code to allow for the replacing of the tables via an import.

The problem is if I have table that has the correct list of tables needed and the table has already been deleted this causing an error - object not found.  What is the workaround.

Karen
try something like this

On Error Resume Next

If IsObject(CurrentDb.TableDefs(tablename)) then
    DoCmd.DeleteObject acTable, tablename
End If
Thanks that works great -

! more issue with the ODBC portion of my code - where do I pass the password in the
 DoCmd.TransferDatabase acImport, "ODBC", "ODBC;DSN=MTR2;UID=" & Chr(34) & fOSUserName & Chr(34) & ";DBQ=MTR2 ;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;;TABLE=" & Chr(34) & nTable & Chr(34) & ", acTable, " & Chr(34) & nTable & Chr(34) & "," & Chr(34) & nTable & Chr(34) & ", False"


Does it go directly after the UID  and if so what is the correct syntax?

Thanks again for all your efforts
ASKER CERTIFIED SOLUTION
Avatar of rockiroads
rockiroads
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial