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

x
?
Solved

VB Code - Editing database

Posted on 1997-09-07
16
Medium Priority
?
202 Views
Last Modified: 2008-02-26
I am trying to edit a table within access from VB.  The code works fine when I run it from VB but when I try to make an EXE. file it tells me "permission is denied".  The error is as "Set Mydb = workspaces......etc etc.  I would realy appreciate some code that will solve this problem
0
Comment
Question by:beams
  • 7
  • 7
  • 2
16 Comments
 
LVL 2

Expert Comment

by:weekee
ID: 1434130
Method 1
You did not create a new workspace before you use to open databases. The createWorkspace method creates a workspace that can be later be used to open databases. When you create a new workspace, that workspace is added automatically to the workspace collection.

Dim myWs as Workspace
Dim myDb as Database
Dim myRec as Recordset

Set myWs = DBEngine.CreateWorkSpace("Workspace_name","UserID","Password")
Set myDb = myWs.Opendatabase("c:\database.mdb",False,False)
Set myRec = myDb.OpenRecordset("select * from table",dbopenDynaset)

Method 2

Use the Application wizard under add_ins menu of VB5 to create a new application. Follows all instructions to create a new application.
0
 

Author Comment

by:beams
ID: 1434131
Add to code now makes not run at all.  The total application only has to edit a table in access 2 with a text field from a form in VB. It works fine from VB. The code is on click - as follows

Private Sub Command1_Click()

Dim Mydb as database, Mytable as recordset, Criteria as string
Set Mydb = Workspaces(0), Opendatabase("C:\User\User.mdb")
Set Mytable = Mydb.OpenRecordset("User List",dbOpenTable)
Criteria = "[ID] =1"
Mytable.Edit
Mytable.("User_Name") = [Text1]
Mytable.Update
Mytable.Close

End Sub
0
 
LVL 2

Expert Comment

by:weekee
ID: 1434132
Trythe following :
1) Restart the computer
2) Open your visual basic program. Do not run the project
3) Try making the exe now.
The problem maybe because when you run the application it alreay use the database hence it lock the database to prevent access...

OR
Try changing:
Set Mydb = Workspaces(0), Opendatabase("C:\User\User.mdb")
to

Dim myWs as Workspace
dim mydb as database

set myws = dbengine.createworkspace(0)
Set Mydb = myws.Opendatabase("C:\User\User.mdb")

OR
is it that there is a typing error ...

try changing :
Set Mydb = Workspaces(0), Opendatabase("C:\User\User.mdb")

to
Set Mydb = Workspaces(0).Opendatabase("C:\User\User.mdb")


0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:beams
ID: 1434133
set myws = dbengine.createworkspace(0)  creates an error when trying to run "Argument not optional" and does not run.  Have tried changing all lines but in doing so it doesnt run at all.  The code does work fine from VB, it just wont let me make an EXE. I have access 2.0 and VB 4.0.  Perhaps you could try the code yourself and see if you can make an EXE.

Anxiously awaiting.....
0
 
LVL 1

Expert Comment

by:borup
ID: 1434134
Try omit the workspace and open the table as a Dynaset. Often I omit the worspace and that works fine.

Actually the following code is taking from one of my own working CGI-scripts, and modified for your variables.

Another problem could be the space in the table "User List". Try using a underscore instead of a space.

BTW: Remember to close the database when finished!


Private Sub Command1_Click()
  Dim Mydb as database, Mytable as recordset, Criteria as string
  Set Mydb = OpenDatabase(C:\User\User.mdb)
  Set Mytable = Mydb.OpenRecordset("User List", dbOpenDynaset)
  Criteria = "[ID] =1"
  Mytable.Edit
  Mytable.("User_Name") = "Test"
  Mytable.Update
  Mytable.Close
  Mydb.close
End Sub


Please let me know if it works.  
0
 

Author Comment

by:beams
ID: 1434135
Omitting the workspace still works fine within VB.  Unfortunatelly I still cant make an EXE.  The error when trying to make is still in  SET MYdb = ......   "Mydb ="  being highlighted with "Permission Denied".  It is such a tiny piece of code,  I cant believe it wont work as an EXE.
0
 
LVL 1

Expert Comment

by:borup
ID: 1434136
Which custom controls are you using?

If you press ctrl+t you can se which custom controls your program is using.
0
 

Author Comment

by:beams
ID: 1434137
There are no custom controls selected.  Which ones do you suggest.
0
 
LVL 1

Expert Comment

by:borup
ID: 1434138
None.

What about your references?
0
 

Author Comment

by:beams
ID: 1434139
References are :

VB for applications
VB objects and proceedures
Microsoft DAO 3.0 Object Library
Microsoft common dialog box
0
 
LVL 1

Expert Comment

by:borup
ID: 1434140
Hmm.

Really weird. All seems to be ok.

Could you post all of the errormessage you recieve?

0
 

Author Comment

by:beams
ID: 1434141
The only error message I receive is when I try to make an EXE file. All it says is "Permission is Denied".  Running it from VB works without any glitches.  If I delete the Set Mydb = ...line from the code, I can make an EXE. file but of course it doesnt work when I run the EXE,
0
 
LVL 1

Expert Comment

by:borup
ID: 1434142
Wait a sec. I have seen this problem before..... One of my friends couldn't make an executable file when using set mydb=....

Sorry to say it but he choosed to upgrade his version of Visual Basic to Professional.

He was using VB 4.0 Basic Edition. What about you?

Just to be sure you can send the project to me, and I can test it at my VB 4.0 Professional Edition.

johnny@interactive.dk
0
 
LVL 1

Accepted Solution

by:
borup earned 200 total points
ID: 1434143
Hopefully you recieved my files. I had no problems making an executable file here.

Try running it and see - it works fine. No problems with your code.

If you are using Visual Basic 4.0 Basic Edition, that's probably your problem.

As mentioned earlier the only solution I have experienced is to upgrade to the Professional Editon.
0
 
LVL 1

Expert Comment

by:borup
ID: 1434144
It seems like it's a limmitation from Microsoft. Something they have to remove to sell the Basic Edition cheaper than the Proffesionel Edition.
0
 

Author Comment

by:beams
ID: 1434145
Taking your advice I un-installed VB 4.0 but not have version 5.0, I re-installed 4.0.  There must have been some conflict because now it works fine.  I spent many, many hours totally bewildered because I have been able to make EXE files until this particular time.

You are trully an expert.  suck, suck

Thanks again

Peter
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

580 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