Solved

VB Code - Editing database

Posted on 1997-09-07
16
191 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Suggested Solutions

Title # Comments Views Activity
SSRS expression Issue finding a string 10 91
Add and format columns in vb6 7 63
Sub or Function is not defined 6 43
VBA: loop recent folder and copy txt file. 8 36
Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
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…
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…

828 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