Solved

How to update Web Access Database field using VB

Posted on 2009-07-06
11
172 Views
Last Modified: 2012-05-07
I want to have a simple Access Database with my web pages and have a field that gets incremented everytime the program is installed.  Or a new record could be added with the user name and date. I already have a registration routine and know how to make the database as well as upload it, but do not know the instructions to change the value of a Access field or how to specifically reference the field through VB once it is located with web pages. Can you please give me a example using VB Express 2008 and VB 6.0, Thank You
0
Comment
Question by:GarySB
  • 6
  • 5
11 Comments
 
LVL 84
Comment Utility
You cannot connect to a remote Access database. If you own the webserver, you could possibly create a VPN solution that would do this, but even then this would be an iffy concept. Or, you could build a simple ASP.NET page that could do this for you.

Or, move your data to a server-type database, like MS SQL Server, MySQL etc ... you can connect to remote instances of those engines via an IP address and standard connection string.
0
 

Author Comment

by:GarySB
Comment Utility
Alright, so your saying that it could be accomplished using MS SQL Server. My goal is simply to be able to tell how many times the program is being installed.  Do you know of a different way of accompilshing this?  Right now my program references fields in the .html web page and reads them into variables in the program.  So say for instance one variable is equal to 1.  I want to now make it 2 and write it back.  Either I need the ability to write/update the .html web page or write to a MS SQL Server database or since I have all the variables I just read in make a new .html page and upload it using the date as part of the name.  Please give me your opinion, Thank You
0
 
LVL 84
Comment Utility
Yes, you could do this with SQL Server (or MySQL, or Oracle, or any other database that supports remote connections).

I'm not really sure what your goal is, but there are much better ways of protecting your app (if that's what you're doing) than this:  

http://www.ssware.com/cryptolicensing/cryptolicensing_ocx.htm

0
 

Author Comment

by:GarySB
Comment Utility
Do you have an example of instruction to change a field?  Thank You and I will look into the link you gave, but right now I wish to complete the work I've started.  I'll increase the points to 500 for you.
0
 

Author Comment

by:GarySB
Comment Utility
My goal is to be able to see how many times the program has been installed and by changing a value denying future installaitons.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 84
Comment Utility
You simply build a connection to your web-based database, then execute a query:

Dim con As ADODB.connection
Set con = New ADODB.Connection

con.Open "Your connect string"

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM SomeTable", con

If Not (rst.EOF and rst.BOF) Then
  If rst("YourField") = 30 Then
    Msgbox "This has been installed too many times"
    Application.Quit
  End If
End If

Set rst = Nothing
con.Close
Set Con = Nothing
0
 
LVL 84
Comment Utility
Sorry ... hit submit too quick ...

To build your connection:

con.Open "ODBC;Driver={SQL Native Client};" &
           "Server=MyServerName;" & _
           "Database=myDatabaseName;" & _
           "Uid=myUsername;" & _
           "Pwd=myPassword"

Obviously you'd have to change MySerName, MyDatabaseName, MyUserName and My Password to match your own

This came from here: http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForSQLServer

There are also strings for other db engines as well.
0
 

Author Comment

by:GarySB
Comment Utility
Thank You, Last thing (I updated points already)
 If rst("YourField") = 30 Then
is good for testing maximum installations, but what would increment "YourField" each time its installed?  That is what I'm essentially looking for, Thank You
0
 

Author Comment

by:GarySB
Comment Utility
Something like:
 If rst("YourField") = 30 Then
    Msgbox "This has been installed too many times"
    Application.Quit
Else
    rst("YourField") =  rst("YourField") + 1
  End If
How do I update  rst("YourField")?
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
Dim con As ADODB.connection
Set con = New ADODB.Connection

con.Open "Your connect string"

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM SomeTable", con, adOpenKeySet, adLockOptimistic

If Not (rst.EOF and rst.BOF) Then
  If rst("YourField") = 30 Then
    Msgbox "This has been installed too many times"
    Application.Quit
  Else
    rst("YourField") = rst("YourField") + 1
    rst.Update
  End If
Else
  '/WHAT ABOUT IF THE APP CANNOT CONNECT
End If

Set rst = Nothing
con.Close
Set Con = Nothing
0
 

Author Closing Comment

by:GarySB
Comment Utility
Thank You!!!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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…
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…

771 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

11 Experts available now in Live!

Get 1:1 Help Now