Not able DELETE / INSERT in Remote Database

[ria_arora]

Dear All,

I'm executing below simple SQL to delete the data from Remote Database but somehow it's not deleting the data.

        strDeleteSQL = "DELETE FROM " & sTableName
        gsCnPDatabase.Execute (strDeleteSQL)

Code for Remote database

Option Compare Database
Global gsCnPDatabase As DAO.Database            'CnP Secured MS Access Database
Global gsCnPDatabaseOpened As Boolean   'Assign Value TRUE once MS Access DB connection is opened
 
'Function is used to connect Secured ACCESS Database and extract CnP database information and assign True value to global variable
Public Function ConnectCnpDatabase() As Boolean
    Dim sCnPDatabaseName As String
    Dim sDatabasePassword As String
    
    On Error GoTo ErrConnectDB
    ConnectCnpDatabase = False
    sCnPDatabaseName = ExtractDatabaseDetails.GetDatabasePath(gsCnPDatabaseID) & "\" & ExtractDatabaseDetails.GetDatabaseName(gsCnPDatabaseID)
    sCnPDatabasePassword = GetDatabasePassword(gsCnPDatabaseID)
    
    ' Create Microsoft Jet Workspace object.
    Set gCurWS = DBEngine.Workspaces(0)
    Set gsCnPDatabase = gCurWS.OpenDatabase(sCnPDatabaseName, False, False, "MS Access;PWD=" & sCnPDatabasePassword)
    ConnectCnpDatabase = True
    gsCnPDatabaseOpened = True  'Assign TRUE once MS Access Secured Database is Initialised
    
    Exit Function
ErrConnectDB:
    gsDBErr = Err.Description
End Function
'Disconnect Secured Access Database
Public Sub DisconnectCnPDatabase()
    If gsCnPDatabaseOpened = True Then  'If MS Access Secured Database is opened
        gsCnPDatabase.Close
        gsCnPDatabaseOpened = False
    End If
    Set gsCnPDatabase = Nothing
End Sub[\code]

 

[Uncle Gizmo]

Dear All,

I'm executing below simple SQL to delete the data from Remote Database but somehow it's not deleting the data.

        strDeleteSQL = "DELETE FROM " & sTableName
        gsCnPDatabase.Execute (strDeleteSQL)

Code for Remote database

Option Compare Database
Global gsCnPDatabase As DAO.Database            'CnP Secured MS Access Database
Global gsCnPDatabaseOpened As Boolean   'Assign Value TRUE once MS Access DB connection is opened
 
'Function is used to connect Secured ACCESS Database and extract CnP database information and assign True value to global variable
Public Function ConnectCnpDatabase() As Boolean
    Dim sCnPDatabaseName As String
    Dim sDatabasePassword As String
    
    On Error GoTo ErrConnectDB
    ConnectCnpDatabase = False
    sCnPDatabaseName = ExtractDatabaseDetails.GetDatabasePath(gsCnPDatabaseID) & "\" & ExtractDatabaseDetails.GetDatabaseName(gsCnPDatabaseID)
    sCnPDatabasePassword = GetDatabasePassword(gsCnPDatabaseID)
    
    ' Create Microsoft Jet Workspace object.
    Set gCurWS = DBEngine.Workspaces(0)
    Set gsCnPDatabase = gCurWS.OpenDatabase(sCnPDatabaseName, False, False, "MS Access;PWD=" & sCnPDatabasePassword)
    ConnectCnpDatabase = True
    gsCnPDatabaseOpened = True  'Assign TRUE once MS Access Secured Database is Initialised
    
    Exit Function
ErrConnectDB:
    gsDBErr = Err.Description
End Function
'Disconnect Secured Access Database
Public Sub DisconnectCnPDatabase()
    If gsCnPDatabaseOpened = True Then  'If MS Access Secured Database is opened
        gsCnPDatabase.Close
        gsCnPDatabaseOpened = False
    End If
    Set gsCnPDatabase = Nothing
End Sub[\code][/QUOTE]

I can't see where you are executing the SQL statement?

 

[ria_arora]

It's working fine. Not sure what happened. I did not change the code, next day it start working,

Thanks for the help

 

[Khalid_Afridi]

It's working fine. Not sure what happened. I did not change the code, next day it start working,

Thanks for the help

The magician has done it overnight

 

[ria_arora]

Yes Khalid after all it's Microsoft product. Some time need to restart the PC.

 

[boblarson]

Just a quick word for you (based on your global variables).

The keyword Global is only around for backwards compatibility. You should use

Public YourVariableName As VariableType

when declaring global variables. That is the more current method. There is no telling if they will drop Global from VBA at any point so it is a good idea to move to that as you do new programming.

 

[ria_arora]

Thanks Bob for the good suggestion

 

[smig]

shouldn't it be:
strDeleteSQL = "DELETE * FROM " & sTableName

 

[ria_arora]

smig

You can use *, but without * also works fine.

It's like when you want to count number of records instead of
SELECT COUNT(*) FROM TableName

You can write
SELECT COUNT('x') FROM TableName

and that will be faster then *.