We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

VB Code - Editing database

beams
beams asked
on
Medium Priority
223 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
Comment
Watch Question

Commented:
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.

Author

Commented:
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

Commented:
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")


Author

Commented:
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.....

Commented:
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.  

Author

Commented:
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.

Commented:
Which custom controls are you using?

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

Author

Commented:
There are no custom controls selected.  Which ones do you suggest.

Commented:
None.

What about your references?

Author

Commented:
References are :

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

Commented:
Hmm.

Really weird. All seems to be ok.

Could you post all of the errormessage you recieve?

Author

Commented:
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,

Commented:
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
Commented:
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

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

Author

Commented:
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.