Solved

How to update Web Access Database field using VB

Posted on 2009-07-06
11
174 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
ID: 24803059
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
ID: 24813258
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
ID: 24814177
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:GarySB
ID: 24860761
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
ID: 24861292
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
 
LVL 84
ID: 24861605
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
ID: 24861631
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
ID: 24862131
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
ID: 24862322
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
ID: 24864018
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
ID: 31600124
Thank You!!!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Dsum Function for List Box Data 7 47
Search for text in a .txt file 14 44
User Level Security 6 38
vb.net and creating a class 5 17
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

777 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