Solved

VB Code - Editing database

Posted on 1997-09-07
16
182 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
 

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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…
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 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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now