Solved

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

Posted on 2012-03-29
9
299 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
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 500 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

809 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