Compile error.

[vijip2001]

Gurus,


My computer has an application running on Access 2003(coded in VBA). My client's computer has Access 2000.
When i take the same application and run on my client's computer, i initially got missing reference ADO 2.5/3.5. So i added the Microsoft DAO 3.6 library and fixed it. Now when i try to update a recordset, it gives error:
"Compile error: Method or data member not found".
But it runs perfect on my computer though.
What am i missing here? Please help.

Thanks,
Viji

 

[xlbo]

.....telepathy on........

nope - can't read your mind - maybe posting the code it errors on might help!

You are on 2003

Your client is on 2000

Probably the issue is that you are using a property or method that is not available in 2000 but as above, we can;t help unless we can see what is causing the error

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[vijip2001]

Sorry..Let me see if i can explain more..Let me know if i am not clear..

I have an application that is in microsoft access 2000. The program has been working fine. Now since the database servers are changing,the tnsnames are changing.So
i copied those program on to my computer, which has access 2003 and i changed the database name in the ODBC connection DSN. I relinked all the tables from the new ODBC connection. I have not changed any code. The username and password has enough permissions to update the tables in the new database. It works perfeect on my computer.
But when i put the code on my client's pc, initially it gave library reference error ' MIssing: ADO2.5/2.5 library'. So i added the reference 'ADO 3.6 library' and that error is gone.
Now when try to query and update using recordset, i am getting the error ' compile error: method or data membet not found'. This works good on my computer(having access 2003).

Here is the code, where it is erroring out.

A query is made to find the record in the bthat is having the gate_criteria. I have verified that the record exists in the database. But the program still goes into
'If rs.NoMatch Then' and throws the error at the line
'rs.last_ticket_number = TRANS_SEQ'

TRANS_SEQ = CLng(Trim$(txtTICKETNO.value))
Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset("SCRAP_GATE_TICKET_MASTER", DB_OPEN_DYNASET)
GATE = "M"
GATE_CRITERIA = "GATE = '" & GATE & "'"
rs.FindFirst GATE_CRITERIA
If rs.NoMatch Then
rs.AddNew
rs.last_ticket_number = TRANS_SEQ
rs.GATE = "M"
rs.Update
Else
COUNT = rs.RecordCount
If COUNT > 0 Then
rs.Edit
rs.last_ticket_number = TRANS_SEQ
rs.GATE = "M"
rs.Update
End If
End If
rs.Close


How should i fix this problem? Please help.

Thanks,
Viji

 

[combo]

Compare references priority or dim variables with library name. In case of type conflict vba takes the highest priority one. Recordset in DAO has NoMatch property, in ADO - no.
Where is 'last_ticket_number' from?

combo

 

[PHV]

Use the following declaration:

Dim rs As [!]DAO.[/!]Recordset

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vijip2001]

I am not sure which references you want me to compare.
'last_ticket_number' is a database column. The trans_seq number is generated in the application and assigned to the 'last_ticket_number' column.

I have tried DAO.recordset. It does not work either.

Another thing, when i debug the code, the debugger does not work either. I put my breakpoints just above the place where it throws error. But it directly goes to the error. How can that happen? I don't understand that.

Thanks,
Viji

 

[PHV]

It does not work either
Any error message ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vijip2001]

Same error message "compile error: method or data mamber not found"

Thanks,
Viji

 

[combo]

References priority: Tools>References, move reference up/down.
Compile error: replace 'rs.last_ticket_number' by collection reference ('rs!last_ticket_number').

combo

 

[vijip2001]

Am i suppose to add any specific reference??

Since the application has lots of places where i need to do it, is there any way i can globally define a collection reference?
When i try to replace 'rs.last_ticket_number' by collection reference ('rs!last_ticket_number') in one place, then it goes to the next line and shows the same old error.

Thanks for all your help,
Viji

 

[RivetHed]

MIssing: ADO2.5/2.5 library'

So i added the Microsoft DAO 3.6 library

ADO and DAO are not the same thing.

 

[combo]

You need to correct the syntax. Was your code working in access 2003 at all (with DAO recordset)?
You try to refer to the field. Reference: rs.FieldName is not valid. After ms DAO help, you can try one of (after rs.):
Fields(0)
Fields("name")
Fields![name]
For the Recordset object Fields is the default collection, so you can simplify the syntax (remove '.Fields'). You can use any of the above that is most useful for you.


combo

 

[vijip2001]

Yes. I knew ADO is not same as DAO.
But i am not getting any option for ADO libraries in the reference options. It lists onle DAO 3.6 as an option. Is there any way to add that reference?

Yes, the code does work with Access 2003(with DAO recordset).

I will try to use the syntax options that you have provided though.

Thanks,
Subha

 

[PHV]

Microsoft ActiveX Data Objects 2.5

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vijip2001]

Added the reference 'Microsoft ActiveX Data Objects 2.5 library' and still gives the same old error.

Thanks,
Viji

 

[combo]

Viji,
you have the same problem for over two years. I have limited experience with programming ADO/DAO in VB(A), but, at least until ADO 2.8 / DAO 3.6, I can see that VBA does not recognise pointing to recordset fields in the way you try to do (rs.GATE = "M"). Maybe there is any trick or it is possible in higher access versions, but in my case (xp) I have the same syntax error. My initial action in such case would be to follow intellisense or help file hints.

When you copy the code it is important (in general) to have the same references in the same order. If you have problems to find the file, open object browser and select library. At the bottom you can see the description and full path (similar to the one in references dialog). You can point it as reference on another machine (if the file exists).
You can open the database in 2k environment and check if any reference is missing. In this case the behaviour of your code is unpredictible, you can be informed that standard vba functions are not defined for instance.

In ms access you can downgrade the database by saving it in old format and use lower version references in vba.

combo