Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VBS and compacting access db? 2

Status
Not open for further replies.

gacaccia

Technical User
May 15, 2002
258
US
hi all,

two questions that are related. first, i have an access database as the backend for a web site. i'm using asp and vbscript to input/output data. how do i compact/repair the database using ado?

second question, does anyone know of a good resource/link for documentation on server.createobject? specifically, i'm looking for a list of what objects are typically available on a server to use with this method. i'm familiar with adodb.connection and adodb.recordset, but i'd love to have a link to a complete list.

thanks,

glenn
 
sfriday,

the first link was exactly what i was looking for, though it unfortunately falls short for my needs. here's the code from the link (these links expire sometimes) for future reference. i modified it slightly to correct a typo and include the use of Server.MapPath...

Code:
function fileExists ( filepath )
    fileExists = objFSO.FileExists ( filepath )
end function

function fileDelete ( filepath )
    fileDelete = objFSO.DeleteFile ( filepath )
end function

Dim objJet 
Dim objFSO
Dim f
Dim f_New

' Assign values for source and destination
f = Server.MapPath("databasename.mdb")
f_New = Server.MapPath("newdatabasename.mdb")

' Object instantiation
Set objJet = Server.CreateObject ("JRO.JetEngine")
Set objFSO = Server.CreateObject ("Scripting.FileSystemObject") 

' Make sure there isn't already a file with the 
'name of the compacted database. 
If fileExists ( f_New ) Then
    fileDelete ( f_New )
End If
 
' Compact the database into the destination location
objJet.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0; " &_
                        "Data Source=" & f & ";", _ 
                        "Provider=Microsoft.Jet.OLEDB.4.0; " &_
                        "Data Source=" & f_New & ";"

' Delete the original database
fileDelete ( f )

'Rename the file back to the original name 
objFSO.MoveFile f_New, f

'Clean-up
Set objFSO = Nothing
Set objJet = Nothing

the reason this code falls short of my needs is that my database is secured (with user permissions) and has a companion mdw file. the above code crashes due to insufficient permissions. anyone know how to pass the mdw, user id and password info to the compactdatabase function?

thanks,

glenn
 
well, finally found my answer. for the record, here it is. the additional parameters i was looking for were appropriately enough, "User ID", "Password" and "Jet OLEDB:System Database"

Code:
function fileExists ( filepath )
    fileExists = objFSO.FileExists ( filepath )
end function

function fileDelete ( filepath )
    fileDelete = objFSO.DeleteFile ( filepath )
end function

Dim objJet 
Dim objFSO
Dim f
dim s
Dim f_New

' Assign values for source and destination
f = Server.MapPath("databasename.mdb")
f_New = Server.MapPath("compactedDB.mdb")
s = Server.MapPath("workgroupfilename.mdw")
' Object instantiation
Set objJet = Server.CreateObject ("JRO.JetEngine")
Set objFSO = Server.CreateObject ("Scripting.FileSystemObject") 
' Make sure there isn't already a file with the 
'name of the compacted database. 
If fileExists ( f_New ) Then
   fileDelete ( f_New )
End If
 objJet.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0; " &_
         "Data Source=" & f & ";" &_
	"Jet OLEDB:System Database=" & s & ";" &_
	"Jet OLEDB:Encrypt Database=false;" &_
	"User ID=userID;" &_
	"Password=userpassword;", _ 
         "Provider=Microsoft.Jet.OLEDB.4.0; " &_
         "Data Source=" & f_New & ";"
' Delete the original database 
fileDelete ( f )
'Rename the file back to the original name 
objFSO.MoveFile f_New, f
'Clean-up
Set objFSO = Nothing
Set objJet = Nothing

works like a charm.

glenn
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top