Karen Schaefer
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_Fr om_MTR2()
On Error GoTo Get_Latest_Metrica_Data_Fr om_MTR2_Er r
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(st rSql)
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;Q TO=T;FRC=1 0;FDL=10;L OB=T;RST=T ;GDE=F;FRL =F;BAM=IfA llSuccessf ul;MTS=F;M DI=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_Fr om_MTR2_Ex it:
Exit Function
Get_Latest_Metrica_Data_Fr om_MTR2_Er r:
MsgBox Error$
Resume Get_Latest_Metrica_Data_Fr om_MTR2_Ex it
End Function
Thanks,
Karen
Function Get_Latest_Metrica_Data_Fr
On Error GoTo Get_Latest_Metrica_Data_Fr
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(st
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;Q
Else
Exit Function
End If
End If
Get_Latest_Metrica_Data_Fr
Exit Function
Get_Latest_Metrica_Data_Fr
MsgBox Error$
Resume Get_Latest_Metrica_Data_Fr
End Function
what is this line
DoCmd.DeleteObject acTable, " & chr(34) & nTable & chr(34) & "
Should it not just be
DoCmd.DeleteObject acTable, nTable
DoCmd.DeleteObject acTable, " & chr(34) & nTable & chr(34) & "
Should it not just be
DoCmd.DeleteObject acTable, nTable
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));"
" 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
on which line?
also, what is the following procedure doing (maybe the error occurs in there)
fOSUserName
ASKER
Thanks tried that still getting the object variable error.
Thanks,
Karen
Function Get_Latest_Metrica_Data_Fr om_MTR2()
On Error GoTo Get_Latest_Metrica_Data_Fr om_MTR2_Er r
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(st rSql)
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;Q TO=T;FRC=1 0;FDL=10;L OB=T;RST=T ;GDE=F;FRL =F;BAM=IfA llSuccessf ul;MTS=F;M DI=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_Fr om_MTR2_Ex it:
Exit Function
Get_Latest_Metrica_Data_Fr om_MTR2_Er r:
MsgBox Error$
Resume Get_Latest_Metrica_Data_Fr om_MTR2_Ex it
End Function
Thanks,
Karen
Function Get_Latest_Metrica_Data_Fr
On Error GoTo Get_Latest_Metrica_Data_Fr
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(st
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;Q
Else
Exit Function
End If
End If
Get_Latest_Metrica_Data_Fr
Exit Function
Get_Latest_Metrica_Data_Fr
MsgBox Error$
Resume Get_Latest_Metrica_Data_Fr
End Function
ASKER
this fOSUserName is function to get current user id from api call.
K
K
ASKER
It is dying on the
Set rs = currentdb.OpenRecordset(st rSql) portion of the code.
k
Set rs = currentdb.OpenRecordset(st
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
u are defining it
get rid of this, what happens
Dim currentdb As Database
>It is dying on the
>Set rs = currentdb.OpenRecordset(st rSql) 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)?
>Set rs = currentdb.OpenRecordset(st
this means that currentdb is not set.
do you use a Access Database Project by any chance (and not a "regular" access database)?
ASKER
changed it to curdb as database with same results - still get error.
K
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
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
ASKER
regular access what is the proper syntax for declaring the current database and for passing the sql statement as the recordset.
Thanks,
Karen
Thanks,
Karen
What I usually do is this
set rs = currentdb.OpenRecordset ....
set rs = currentdb.OpenRecordset ....
if u notice, in your code, u are doing it correctly
Set rs = currentdb.OpenRecordset(st rSql)
All u have to try is removing the definition
Dim currendb
and nothing else
Set rs = currentdb.OpenRecordset(st
All u have to try is removing the definition
Dim currendb
and nothing else
ASKER
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
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.TableDe fs(tablena me)) then
DoCmd.DeleteObject acTable, tablename
End If
On Error Resume Next
If IsObject(CurrentDb.TableDe
DoCmd.DeleteObject acTable, tablename
End If
ASKER
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;Q TO=T;FRC=1 0;FDL=10;L OB=T;RST=T ;GDE=F;FRL =F;BAM=IfA llSuccessf ul;MTS=F;M DI=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
! 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;Q
Does it go directly after the UID and if so what is the correct syntax?
Thanks again for all your efforts
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
strSql = "SELECT Name, Type" & _
" FROM MSysObjects" & _
" WHERE (((Name) Not Like ""*M*"") AND ((Type)=1));"