?
Solved

VB access code envorinment setup

Posted on 2003-03-05
17
Medium Priority
?
191 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
[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
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month10 days, 4 hours left to enroll

762 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