Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Does a VBScript editing an Access database run with 2007 Runtime?

Posted on 2012-08-15
5
Medium Priority
?
522 Views
Last Modified: 2012-08-17
Does anyone know if the following vbScript code will work on a PC that only has MS Access 2007 runtime, not the full MS Access?

   Dim appAccess
   Dim strDBNameAndPath
   Dim dbe
   Dim dbs
   Dim strSQL

   Set appAccess = _
      WScript.CreateObject("Access.Application")
   Set fso = _
      WScript.CreateObject("Scripting.FileSystemObject")
   strDBNameAndPath= "C:myFolder\myApp.mdb"
   
On Error Resume Next

   Set fil = fso.GetFile(strDBNameAndPath)
   If fil Is Nothing Then
      strPrompt = "Can't find " & strDBNameAndPath& _
         "; canceling"
      MsgBox strPrompt, vbCritical + vbOKOnly
      Quit
   Else
      Set dbe = appAccess.DBEngine
      Set dbs = dbe.OpenDatabase(strDBNameAndPath)
   
      Set rst = dbs.OpenRecordset("tblMyTable")
      Do Until rst.EOF
         rst.edit
       rst.[Description] = rst.[Description] & "."
       rst.update
      rst.MoveNext
      Loop
      rst.Close
   
   End If

   Set rst = Nothing
   Set appAccess = Nothing
0
Comment
Question by:Rob4077
  • 3
  • 2
5 Comments
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 38299016
No it will not run on a machine without access installed since you are using the access object in your code ( Set appAccess = WScript.CreateObject("Access.Application").  That creation step requires Access.  You can re-write this using ADO to get access to the database.  Let me know if you need help with getting it into new code.  If I get a chance I may just do it anyway.
0
 
LVL 20

Accepted Solution

by:
ltlbearand3 earned 2000 total points
ID: 38299075
You can try something like this.  It should work on a machine without access installed as long as the mdb file is in the right location.

	Dim objConn, objRS
	Dim strDBNameAndPath
	Dim dbe
	Dim dbs
	Dim strSQL
	
	' ADO Connection Objects
	Set objConn = CreateObject("ADODB.Connection")
	Set objRS = CreateObject("ADODB.Recordset")
	Set fso = _
		WScript.CreateObject("Scripting.FileSystemObject")
	strDBNameAndPath= "e:\EETest\eetest.mdb"
   
	On Error Resume Next

	Set fil = fso.GetFile(strDBNameAndPath)
	If fil Is Nothing Then
		strPrompt = "Can't find " & strDBNameAndPath& _
			"; canceling"
		MsgBox strPrompt, vbCritical + vbOKOnly
		Quit
	Else
		' Connect to the Access Database
		objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBNameAndPath & ";"

		' Set the records set to the Access Connection
		Set objRS.ActiveConnection = objConn

		' Set the properties for the Recordset and get the data
		with objRS			
			.LockType = 3 ' adLockOptimistic
			.CursorType = adOpenKeyset 
			' We only need the description column
			.Source = "Select Description from tblMyTable" 
			.Open
		End with
		
		' Loop Through the records
		Do While not objRS.EOF
			' Build Update Command
			strSQL = "Update tblMyTable Set Description = '" & objRS("Description") & ".'" 

			' Set the new Value
			objRS("Description") = objRS("Description") & "."
			
			' Update this Record
			objRS.Update

			' Move to the Next Record
			objRS.MoveNext
		Loop

		objRS.Close
	End If

Set objRs = nothing
Set objConn = Nothing

Open in new window


-Bear
0
 

Author Comment

by:Rob4077
ID: 38299123
Hi Bear, That appears to be just what I need. You said << It should work on a machine without access installed >>. You mean as long as the runtime version is installed or will it work even without runtime installed? Is it going to foul up registries if there machine has both runtime 2007 and another version of MS Access installed? It looks to me like this will be a perfect solution for me if it works that way.
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 38299250
The could is not fully tested and that is what I mean by should.  It uses the Jet driver which will be on many machines by default, but you may need to install the run time on some.  You will need to just test just to verify.
0
 

Author Comment

by:Rob4077
ID: 38299509
That is fantastic. I will test and refine it, no problems. I only asked in case you knew. Appreciate your kind assistance.


Rob
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

In this article we want to have a look at the directory attributes which are used by Microsoft to store the so called Security Identifiers (SID). These SIDs plays an important role in delegating and granting permissions and in authentication of trus…
I met Paul Devereux (@pdevereux) today when I responded to his tweet asking “Anybody know how to automate adding files from disk to a folder in #outlook  ?”.  I replied back and told Paul that using automation, in this case scripting, to add files t…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

581 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