Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VB access code envorinment setup

Posted on 2003-03-05
17
Medium Priority
?
196 Views
Last Modified: 2010-05-01
This simple code works in Access VB envorinment. I am trying to move it to a basic script in Automate. I can't get the thing to work. I have added all the necessary libraries.

Public Sub main()
Dim num As Variant
num = DMax("a", "Table1")
MsgBox num
End Sub


Code in Automate basic script:

Sub Main()

Dim JasonDatabase As Database
Dim JasonTable As Recordset
Dim Num As Integer
Dim ws As Workspace
Dim tdf As TableDef

Set ws = DBEngine.Workspaces(0)
Set JasonDatabase = ws.OpenDatabase("C:\Documents and Settings\Jason\My Documents\test\test.mdb")
Set tdf = JasonDatabase.TableDefs("Table1")
Num = DMax("b", "Table1")
MsgBox (Num)
End Sub

This code bombs on line
Num = DMax("b", "Table1")
with "Reserved error"

Can anybody tell me what's wrong or what i have to do or load?

thanks


0
Comment
Question by:storkuk79
  • 9
  • 7
17 Comments
 
LVL 2

Expert Comment

by:Jacamar
ID: 8077409
try
dim Num as Variant as you had in your first sub
0
 
LVL 3

Expert Comment

by:Diveblue
ID: 8077410
DMax is only available within access. Where are you trying to run this code? I'm not sure what you mean by "Automate basic script". What environment are you coding in?

scott
0
 

Author Comment

by:storkuk79
ID: 8077453
Jacamar:
dim Num as Variant doesn't work

Scott:
I am running it in basic script envorinment that is attached to Automate. I know Dmax is only available within access. I have added "Microsoft Access 10.0 Library" in references. Still no go.

Another suggestions?
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.

 

Author Comment

by:storkuk79
ID: 8077470
Jacamar:
If i change to Dim num as Integer, it bombs on

Msgbox(num) with error message "wrong number of parameters"
0
 
LVL 3

Accepted Solution

by:
Diveblue earned 240 total points
ID: 8077476
it looks like you're trying to do this in VB, s try this...

Sub Main()

Dim JasonDatabase As Database
Dim JasonTable As Recordset
Dim Num As Integer
Dim ws As Workspace
Dim tdf As TableDef
Dim strSql as String

Set ws = DBEngine.Workspaces(0)
Set JasonDatabase = ws.OpenDatabase("C:\Documents and Settings\Jason\My Documents\test\test.mdb")
strSql="SELECT top 1 b FROM Table1 ORDER BY b DESC;"
set JasonTable = JasonDatabase..OpenRecordset(strSql)
JasonTable.MoveFirst
Num = JasonTable.Fields("b").value
MsgBox (Num)
End Sub
0
 

Author Comment

by:storkuk79
ID: 8077493
scott,
I am getting a type mismatch error on

set JasonTable = JasonDatabase.OpenRecordset(strSql)
0
 
LVL 3

Expert Comment

by:Diveblue
ID: 8077530
set JasonTable = JasonDatabase..OpenRecordset(strSql) should be
set JasonTable = JasonDatabase.OpenRecordset(strSql)

what is "Automate"?

What version of msAccess are you using?

scott
0
 
LVL 3

Expert Comment

by:Diveblue
ID: 8077540
set JasonTable = JasonDatabase..OpenRecordset(strSql) should be
set JasonTable = JasonDatabase.OpenRecordset(strSql)

what is "Automate"?

What version of msAccess are you using?

scott
0
 

Author Comment

by:storkuk79
ID: 8077597
i took out the extra period and it give me the "type mismatch error"

I am referencing:
Access 10.0 object library
ADO 2.7 Library
DAO 3.6 Object Library

jason
0
 

Author Comment

by:storkuk79
ID: 8077607
automate is a scripting language for windows.

http://www.unisyn.com/automate/
0
 
LVL 3

Expert Comment

by:Diveblue
ID: 8077718
remove the reference to ADO 2.7
0
 

Author Comment

by:storkuk79
ID: 8077735
scott... here is something funny.

I got it to work on accident.
this is how.

1. open automate script. run the original script. it bombs.

2. go to test.mdb in directory and double click on it. Access does not launch.

3. run original script again.... and it works!!!

what's going on here. I must missing some library or some initial setup option or something.

any suggestions?

jason
0
 

Author Comment

by:storkuk79
ID: 8077743
removing ADO doesn't work.
0
 

Author Comment

by:storkuk79
ID: 8077764
removing ADO doesn't work.
0
 
LVL 3

Expert Comment

by:Diveblue
ID: 8079569
remove the reference to ADO 2.7
0
 
LVL 3

Expert Comment

by:Diveblue
ID: 8079634
Sorry...not familiar with Automate, but in dao, the .OpenRecordset method should take a string. check the documentation. Try the same code in a Word or excel macro.

scott
0
 

Author Comment

by:storkuk79
ID: 8096824
scott,

thanks for all of your help.

The code you provide for me worked. It turns out that taking out ADO does work. I didn't know about the priority issue. Thanks for walking me through the stuff.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

581 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