Docmd.RunSQL problem... Access 2000 1

[bondm007]

I used a RunSQL statement like the following in Access97, but Access2K does not like the Left and Right functions? It says they don't exists... Is there a replacement or am I going crazy?

Docmd.RunSQL "SELECT [EN], (Left([EN],6) & Right([EN],4)) AS ESMA INTO [New Table] FROM [Old Table];"

This worked in Access97...Why does Access2K not like the Left and Right functions. Even the next statement is coming up with an error.

Docmd.RunSQL "UPDATE [New Table] SET [ESMA] = Left([EN],6) & Right([EN], 4);"


Mike

 

[thornmastr]

Sounds like you are using the A2K default which is ADO not DAO. Check your references and make sure you have a reference to DAO 3.51. you might even remove the reference to ADO unless you plan to use it.

Robert Berman

 

[THoey]

You need to check your references. Open a module, go to Tools -> references. Look for any that are missing. You may also have to change the order a bit. If you do a search here for "references" you will see this is a problem with converting databases and has been asked/answered lots of times. Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[bondm007]

Thanks... I checked the references and Microsoft DAO 3.6 Object Library is the third item in the list under Visual Basic for Applications and Microsoft Access 9.0 Object Library...

This is the error message I am getting:

Runtime error '3085':

Undefined function 'Left' in expression.


Mike

 

[Rhonin]

I am working on a similiar problem, with the "Trim" function being used in a case select statement.
Okay in 97, 2k says no.
This is at work.
I will take the same database home and it runs fine.

At work I have MSOffice 2K Proffesional, at home developers version.

Is there some difference?

Rhonin

 

[thornmastr]

It is a reference problem. Now that you know you have DAO 3.6 checked, highlight it and use the up arrow until it won’t move higher. Look for a checked reference to an ADO library. If you do not need ADO uncheck it.

However, if you plan to use both DAO AND ADO, leave the ADO reference, but, in your code, you must specify what group you want to use amongst shared definitions, ie, database and record set, ie,
Dim db as DAO.database
Dim rs as DAO.recordset.

That should eliminate the problem.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[experienced]

Yes, it is a reference problem. All you have to do is to refresh the references. This is how to refresh the references:

1. Open the references at Tools in VBA module
2. Add any new reference (you will remove it soon)
3. Save the references
4. Open them again and remove the one you just added
5. Save the references

When you installed a program from Microsoft recently, it automatically upgraded Data Access which uses a different data type from that of Acc97 or old Acc2K.

 

[bondm007]

Thank-you for all your help. When I converted the database from Access97 to Access2K the Microsoft ActiveX Data Objects 2.5 Library was not a reference. I have added it as a reference and placed it at the bottom of the checked list. The SQL statement now runs without a hitch...


Mike