UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Ado To Insert Into Access, Office 2010    
 
   
WildBird
post Nov 7 2019, 03:12 PM
Post#1


UtterAccess VIP
Posts: 3,630
Joined: 19-August 03
From: Auckland, Little Australia


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


--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
cheekybuddha
post Nov 7 2019, 04:02 PM
Post#2


UtterAccess VIP
Posts: 11,676
Joined: 6-December 03
From: Telegraph Hill


CODE
cnn.Execute = "DELETE * FROM tblOpenAudit"


Are you sure this works, Coop?

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

Any errors being logged?

--------------------


Regards,

David Marten
Go to the top of the page
 
WildBird
post Nov 7 2019, 06:14 PM
Post#3


UtterAccess VIP
Posts: 3,630
Joined: 19-August 03
From: Auckland, Little Australia


QUOTE
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....




--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
WildBird
post Nov 7 2019, 07:51 PM
Post#4


UtterAccess VIP
Posts: 3,630
Joined: 19-August 03
From: Auckland, Little Australia


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


--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
DanielPineault
post Nov 7 2019, 08:03 PM
Post#5


UtterAccess VIP
Posts: 6,961
Joined: 30-June 11



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

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
WildBird
post Nov 7 2019, 09:08 PM
Post#6


UtterAccess VIP
Posts: 3,630
Joined: 19-August 03
From: Auckland, Little Australia


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!


--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
DanielPineault
post Nov 7 2019, 09:14 PM
Post#7


UtterAccess VIP
Posts: 6,961
Joined: 30-June 11



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

I think everyone can relate to that!

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th November 2019 - 07:40 PM