Solved

VB Code - Editing database

Posted on 1997-09-07
16
196 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
[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
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 100 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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month4 days, 15 hours left to enroll

636 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