Solved

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

Posted on 2012-03-29
9
309 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

733 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