[X]My Assistant

[WildBird]

Hi All,

I have done this before, but dont have my old code, and cant see what the issue is. I want to execute a SQL statement from Excel, to insert a record into Access.

CODE

Function AuditOpen(ByVal strFileName As String) As Boolean
'Date:          Thursday, 07 November 2019 3:17:04 PM
'Author:        Stephen Cooper
'Email:         XXXXX@XXXXX.com
'Ph:
'In parameters
'Output
'Description:   Will add an entry to the audit db when the file is opened.
'Calls:
'Notes:
'Example:

On Error GoTo HandleError:

AuditOpen = True

Dim intMouseType As Integer
Dim strErrorMsg As String
Dim varReturn As Variant
Dim strUser As String
Dim strDBPath As String
Dim strDBName As String
Dim strSQL As String
Dim cnn As Object
Dim strConnection As String

intMouseType = Application.Cursor

Application.Cursor = xlWait

strUser = GetUser

strDBPath = CheckPath(GetString("DBPath"))
strDBName = GetString("DBName")

strSQL = "INSERT INTO tblOpenAudit (User, FileName) VALUES('" & strUser & "', '" & strFileName & "')"

Set cnn = CreateObject("ADODB.Connection")

strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDBPath & strDBName & ";"

cnn.Open strConnection

cnn.Execute = "DELETE * FROM tblOpenAudit"  

cnn.Execute strSQL

ExitHere:
On Error Resume Next
'Close all recordsets etc here
'varReturn = SysCmd(acSysCmdClearStatus)
Application.Cursor = intMouseType
cnn.Close
Set cnn = Nothing
Exit Function

HandleError:
Select Case Err.Number
Case Else
    LogError "AuditOpen|" & ThisWorkbook.Name & "|" & strErrorMsg & "|" & Err.Number & " - " & Err.Description & "| Line number " & Erl
    MsgBox strErrorMsg & " " & Err.Number & " " & Err.Description, vbInformation, "Error"
    AuditOpen = False
    'DoCmd.Close acForm, strUpdateForm, acSaveNo
    Resume ExitHere
End Select

End Function

A simple
AuditOpen "TestFile"
Should place an entry into the table. I have checked, the strUser, strDBPath, and strDBName all return correct values. The
DELETE * FROM tblOpenAudit
line works as well. If I copy the strSQL and open the database, and execute it, it works fine. Its Friday morning, and I am missing something easy I feel!

Any thoughts?

Cheers

Coop

[cheekybuddha]

CODE

cnn.Execute = "DELETE * FROM tblOpenAudit"

Are you sure this works, Coop?

That ' = ' probably oughtn't to be there!

Any errors being logged?

[WildBird]

Are you sure this works, Coop?

That ' = ' probably oughtn't to be there!

Yeah, I think I had it in correctly, then deleted it, then added it back, but put in the '='.

Anyway, taking the '=' sign out will delete anything in the table, so the connection works.

Copying the SQL string into the database and running it there, works.

CODE

INSERT INTO tblOpenAudit (User, FileName) VALUES('StephenCo', 'test')

It is from Excel that it isnt working.

The code as is will error with
"-2147217900 - Syntax error in INSERT INTO statement"

I changed it to

CODE

cnn.Execute strSQL , , 16

This now doesn't error, but doesn't add any records. My thoughts are it was some parameter I had to use, but cant think of it....

[WildBird]

Ended up using a command instead, and turns out User is a reserved word in ADO it seems, not Access. Changed field name as well

Code that is now working

CODE

Function AuditOpen(ByVal strFileName As String) As Boolean
'Date:          Thursday, 07 November 2019 3:17:04 PM
'Author:        Stephen Cooper
'Email:         XXXXXXX@xxxxxxxxxxxx.com
'Ph:
'In parameters
'Output
'Description:   Will add an entry to the auti db when the file is opened.
'Calls:
'Notes:
'Example:

On Error GoTo HandleError:

AuditOpen = True

Dim intMouseType As Integer
Dim strErrorMsg As String
Dim varReturn As Variant
Dim strUser As String
Dim strDBPath As String
Dim strDBName As String
Dim strSQL As String
Dim cmd As Object
Dim strConnection As String

intMouseType = Application.Cursor

Application.Cursor = xlWait

strUser = GetUser

strDBPath = CheckPath(GetString("DBPath"))
strDBName = GetString("DBName")

strSQL = "INSERT INTO tblOpenAudit (UserName, FileName) VALUES('" & strUser & "', '" & strFileName & "')"

Set cmd = CreateObject("ADODB.Command")

strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDBPath & strDBName & ";"

With cmd
    .ActiveConnection = strConnection
    .CommandText = strSQL
    .Execute
End With

ExitHere:
On Error Resume Next
'Close all recordsets etc here
'varReturn = SysCmd(acSysCmdClearStatus)
Application.Cursor = intMouseType
Set cmd = Nothing
Exit Function

HandleError:
Select Case Err.Number
Case Else
    LogError "AuditOpen|" & ThisWorkbook.Name & "|" & strErrorMsg & "|" & Err.Number & " - " & Err.Description & "| Line number " & Erl
    'MsgBox strErrorMsg & " " & Err.Number & " " & Err.Description, vbInformation, "Error"
    AuditOpen = False
    'DoCmd.Close acForm, strUpdateForm, acSaveNo
    Resume ExitHere
End Select

End Function

[DanielPineault]

USER is a JET reserved (kb248738), an SQL Server reserved and an ODBC reserved word (kb125948), see http://allenbrowne.com/AppIssueBadWord.html#U

[WildBird]

Cheers Daniel!

I have a bit of code that worked fine last contract, but I didn't bring it with me. I must have used a slightly different name, i.e. 'UserName' back then. Lack of sleep and being a Friday probably means I just used 'User' this time. Anyway, seems to be working OK now. I am doing a quick demo/prototype/proof of concept, and is just frustrating hitting little things like this when I have so much other work to do! I dont do much Excel to Access, but this prototype will be using it a bit, so best get back up to speed on it. Thankfully I have UA!

[DanielPineault]

just frustrating hitting little things like this when I have so much other work to do!

I think everyone can relate to that!