Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2012-03-29
9
Medium Priority
?
334 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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 …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

972 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