PDA

View Full Version : Change records in access database



revilord
02-18-2005, 11:02 AM
I need to have an InstallScript project add the custom information to a table in an MS Access database. I have seen several scripts for connecting to a sql server and updating the tables but I have not been able to do the same for Access.

Does anyone have a simply ado connection script that works for connecting to Access?

For example here is one that I modified from the installsite.org site. It should just verify that the db exists instead it just aborts. I am not even sure what kind of try catch errors I should be looking for.

The debugger gives me an Error "Provider cannot be found. It may not be properly installed." But this is just the last attempt in several.

///////////////////////////////////////////////////////////////////////////////
//
// Function: DoesDatabaseExist
//
// Purpose: This function will determine whether a given database exists.
//
// Arguments: svServerName - The name of the SQL server to run the script on
// svDatabaseName - The name of the SQL database to run the script on
// svDriver - ADO requires this, but for SQL server you just send in "SQL Server"
// svUserName - The system account for SQL server
// svUserPassword - The password for the system account
//
// Usage:
// if (DoesDatabaseExist("SQLServer", "MyDatabase", "SQL Server", "sa", "saPassword") = FALSE) then
// DoesDatabaseExist("0", "C:\\temp\\temp.mdb", "Microsoft.Jet.OLEDB.4.0", "", "temp");
///////////////////////////////////////////////////////////////////////////////
function BOOL DoesDatabaseExist(svEngineType, svDatabaseName, svDriver, svUserName, svUserPassword)
OBJECT pADOConnObj, pADORecordSetObj;
STRING szADOConnObjID, szADORecordSetObjID, szConnString, szSQL;
BOOL bExists;
begin
bExists = FALSE;

// Create ADO Connection Object to connect to the SQL server
szADOConnObjID = "ADODB.Connection";
set pADOConnObj = CreateObject(szADOConnObjID);

// Create the SQL string to complete the connection
szConnString = "Provider={" + svDriver + "};";
szConnString = szConnString + "Engine Type=" + svServerName + ";";
//szConnString = szConnString + "uid=" + svUserName + ";";
szConnString = szConnString + "pwd=" + svUserPassword + ";";
szConnString = szConnString + "data Source=" + svDatabaseName + "";

// Open the ADO Connection
pADOConnObj.Open(szConnString);

// Create ADO Recordset object for the return
szADORecordSetObjID = "ADODB.Recordset";
set pADORecordSetObj = CreateObject(szADORecordSetObjID);

// Set some ADO Recordset properties
pADORecordSetObj.CursorType = 3;
pADORecordSetObj.ActiveConnection = pADOConnObj;

// Create the SQL string to retrieve the database if it exists
szSQL = "Select name from sysdatabases where name='" + svDatabaseName + "'";

// Use the recordset to see if the database exists
pADORecordSetObj.Open(szSQL);
if (pADORecordSetObj.RecordCount = 1) then
bExists = TRUE;
MessageBox("Exists",0);
endif;

return bExists;
end;

Christopher Painter
02-18-2005, 11:12 AM
Something like this in VBSCript... would port to InstallScript easily. ( Note this was writted for a standalone WScript not ActiveScript )

Dim objConnection
Dim objCommand

Startup
ModifyDB
CleanUp 0 ' Exit Success ( DOES NOT RETURN )

Sub StartUp

On Error Resume Next

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")

objConnection.Open "Provider=MSDASQL;" &_
"Driver={Microsoft Access Driver (*.mdb)};" &_
"Dbq=C:\somedatabase.mdb"

If err.Number < 0 Then
CleanUp( -10 )
End If

Set objCommand.ActiveConnection = objConnection

End Sub


End Sub

Sub ModifyDB

On Error Resume Next

objCommand.CommandText = "do sql stuff here"
objCommand.Execute
If err.Number < 0 Then
CleanUp( -20 )
End If

End Sub

Sub CleanUp( intExitCode )

set objCommand = Nothing
set objConnection = Nothing
Wscript.Quit( intExitCode )

End Sub

revilord
02-18-2005, 01:55 PM
Christopher,
Do you want to fly up to Toronto? I think it would be faster if I just had you finish this projects. thanks again I will give it a try.

I just realized that the 6.2 script that already contained the MS Access ado connect to my program worked fine. When I updated it to 10.5 it failed. grrr. I know I should scrap the whole InstallScript project.