none
Database Transaction Support for Access 2013 RRS feed

  • Question

  • I am writing an application using Excel VBA (Excel 2013) which needs to access an Access database (2013) and needs support for database transactions. What data access technologies can I use from Excel VBA that Support database transactions against Access 2013 databases. 
    Monday, August 12, 2013 10:57 AM

Answers

  • Hi Dennis,

    I wrote a sample in Excel 2013. The code in Excel connect Access 2013 and update the table named student.

    And you can get more information about transaction from link below.

    Transaction Processing

    Befor running the code, I will recommeond you to add the refference of Microsoft ActiveX Data Object {version}

    Library like below.

    Sub main()
    Dim myConnection As ADODB.Connection
    Dim myRecordset As ADODB.Recordset
    
    Set myConnection = New ADODB.Connection
    Set myRecordset = New ADODB.Recordset
    
    myConnection.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Documents\study\Access\HelloWord.accdb"
    
    '—Open the connection --
    myConnection.Open
    
    myConnection.Execute ("select * from student")
    myConnection.BeginTrans
    myConnection.Execute ("update student set studentname ='Bony4'")
    'if you don't commit trans the database will not accept update
    myConnection.CommitTrans
    
    
    End Sub


    Fei
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Thursday, August 15, 2013 4:07 PM
    Moderator

All replies

  • Hi CatFelix,

    As far as I know, you can use ADO or DAO in Excel VBA. And both of them support the transaction.

    You can get more information from articles below:

    About ADO Transaction

    How to: Use Transactions in a DAO Recordset


    Fei
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, August 13, 2013 6:39 AM
    Moderator
  • Hi Fei,

    I am aware that DAO provides transaction support, but this is an old deprecated technology which I was hoping to avoid. I also believe that ADO  provided transaction support on previous versions of Access. I am currently without any hardware on which I can trial different things. Are you aware if this support is still provided  against Access 2013 databases?

    Dennis  Jelavic

    Tuesday, August 13, 2013 11:53 PM
  • Hi Dennis,

    I wrote a sample in Excel 2013. The code in Excel connect Access 2013 and update the table named student.

    And you can get more information about transaction from link below.

    Transaction Processing

    Befor running the code, I will recommeond you to add the refference of Microsoft ActiveX Data Object {version}

    Library like below.

    Sub main()
    Dim myConnection As ADODB.Connection
    Dim myRecordset As ADODB.Recordset
    
    Set myConnection = New ADODB.Connection
    Set myRecordset = New ADODB.Recordset
    
    myConnection.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Documents\study\Access\HelloWord.accdb"
    
    '—Open the connection --
    myConnection.Open
    
    myConnection.Execute ("select * from student")
    myConnection.BeginTrans
    myConnection.Execute ("update student set studentname ='Bony4'")
    'if you don't commit trans the database will not accept update
    myConnection.CommitTrans
    
    
    End Sub


    Fei
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Thursday, August 15, 2013 4:07 PM
    Moderator
  • Thanks Fei. You've been a great help. Hopefully I'll have my computer back today and I'll be able to continue on.

    Dennis Jelavic

    Thursday, August 15, 2013 10:08 PM