Access 2003 - Opening a database from a module used in Access 97

I have a function which is used for updating a field in a table.
The database has been converted from 97 to 2003.
The DAO 3.6 Library is ticked under References.
But despite trying to follow other examples I keep getting runtime errors.

I'm sure this is as simple as it gets but it's defeated me as a non-programmer!

Public Function UMailsortExistingCodes()

ENDFILE = "NO"
Dim MYDB As Database
Dim ALPHA As Recordset
Set MYDB = DBEngine.Workspaces(0).Databases(0)
Set ALPHA = MYDB.OpenRecordset("ALPHA", DB_OPEN_TABLE)

ALPHA.Index = "SORT"

ALPHA.MoveFirst

Do Until ALPHA.EOF
    
    With ALPHA
        .Edit
        
        If Not IsNull(ALPHA![CODE]) Then
    
    If Left(ALPHA![POSTCODE], 4) = "MK1 " Then [ALPHA]![CODE] = "14735"
    If Left(ALPHA![POSTCODE], 4) = "MK2 " Then [ALPHA]![CODE] = "14735"
    If Left(ALPHA![POSTCODE], 4) = "MK3 " Then [ALPHA]![CODE] = "14735"
    
        .Update
        
    End If
    
        ALPHA.MoveNext
    End With

Loop

ALPHA.Close

End Function

Open in new window

chrismkAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
You will have to post details of the error/s you are getting.
There is nothing basically wrong with the code you posted.

You can change a couple of lines as below to get them up-to-date..

Dim ALPHA As DAO.Recordset
Set MYDB = DBEngine.Workspaces(0).Databases(0)
Set ALPHA = MYDB.OpenRecordset("ALPHA", dbOpenTable)

but these should not really affect the success of your application.

Open any module and try Debug>Compile and see if you have any errors reported.
chrismkAuthor Commented:
If I try Debug>Compile I get errors from some message statements in the forms section!

I've changed the lines as you suggested.

When I try to run the function it falls over on line 7:
Set ALPHA = MYDB.OpenRecordset("ALPHA", dbOpenTable)

Runtime error 3219
Invalid operation
Rey Obrero (Capricorn1)Commented:
how about doing this

dim rsAlpha as dao.recordset, mydb as dao.database
set mydb=DBEngine.Workspaces(0).Databases(0)
set rsAlpha=mydb.openrecordset("Alpha")
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

clarkscottCommented:
set mydb = codedb

Scott C
peter57rCommented:
Have you also split your database into frontend and backend as well as converting it?
That would be an explanation for that  error message.

I don't like the code in that the name Alpha is used for two different objects; but I don't see that actually causing a problem except in trying to read the code.
clarkscottCommented:
Is the table spelled correctly (hasn't been changed) and is linked or exists in the database that's trying to run the code.?

Also, I haven't use workspaces in a long time...

Dim MYDB AS DAO.DATABASE
Dim ALPHA As DAO.Recordset
Set MYDB = CODEDB  '---- like saying "me.mydatabase".
Set ALPHA = MYDB.OpenRecordset("ALPHA", dbOpenTable)
chrismkAuthor Commented:
Thanks everyone for the response.
Yes the database is split - however, I'm running this from the 'main' copy which contains all the tables.
I'll try some of the other suggestions and get back to you.
However, I'm now at home and not due in the office until Monday so I'll need to use RWW before then to access the server.
peter57rCommented:
You can't use this code on a split database.  It only works for tables that are physically in the same file as the code.

So you need to change these two lines..

Set ALPHA = MYDB.OpenRecordset("ALPHA", DBOPENTABLE)
ALPHA.Index = "SORT"

to...
Set ALPHA = MYDB.OpenRecordset("Select * from ALPHA order by [Sort]", dbopendynaset)

(Delete ALPHA.Index = "SORT")

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chrismkAuthor Commented:
Excellent - that did the trick!
Many thanks everyone.
I've assigned the full amount of points to peter57r because he not only provided the solution but also asked whether the database was split!

Is there a website/ resource out there that highlights some of the more common pitfalls when converting from 97 to 2003? I have to say there have been no real problems in the last 4 months since it was converted but I suspect something else is probably waiting to bite.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.