Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2012-03-29
9
Medium Priority
?
327 Views
Last Modified: 2012-03-29
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

0
Comment
Question by:chrismk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 37782760
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.
0
 

Author Comment

by:chrismk
ID: 37782844
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37782893
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")
0
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
LVL 20

Expert Comment

by:clarkscott
ID: 37782919
set mydb = codedb

Scott C
0
 
LVL 77

Expert Comment

by:peter57r
ID: 37782964
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.
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 37783129
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)
0
 

Author Comment

by:chrismk
ID: 37783338
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.
0
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 37783490
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")
0
 

Author Closing Comment

by:chrismk
ID: 37783877
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.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question