Using an Access, MySQL, PostgreSQL or SQLite database from VBA

Introduction

Note to pedantic guys: yes Access is not a database engine, only the graphical front-end to Jet/ACE, but we’ll stand with this simplification. 🙂

The sample application (Excel + VBA) and the SQL source code are available in this ZIP archive: VBA_Databases_Source.zip.

If you are an advanced VBA developer who builds applications that manage a non trivial amount of data odds are good you are using an Access database to store them.
If this setup perfectly fits your current needs, you completely master it, you’re not experiencing any issue and your needs won’t evolve in the near future you can skip this article and continue enjoying your system. 😉

Indeed, do you really need a new database management system (DBMS)?
Often the only argument in favor of migrating to other DBMS is they are “better”; while it’s true for some technical capabilities, it may not be regarding other “metrics” like simplicity: Access is easy to understand and manage for non IT staff and is often installed with default business workstation setup along with the rest of the Office suite.

So let’s say you have strong reasons to migrate to a new DBMS because you’ve come to the point where you feel you need support for at least one of the following features: interoperability, big storage, high concurrency (hundreds of users) and/or high performance, and Access starts to become a part of the problem.
So what can you do if you want to enhance the quality of your database without making your total cost of ownership (TCO) explode?

Your TCO is essentially made of:

  • licensing costs: limiting them is quite simple: using a free, usually open-source, database and paying only for support
  • management costs: they are by far bigger than the licensing costs and are directly impacted by the complexity of the DBMS; so you need a simple DBMS that you’ll be able to setup and manage yourself as you used to do with Access without the help of system or database administrators
  • development costs: every additional change to your current schema or VBA implementation to fit the new DBMS will have a cost; so we want things to be transparent with zero additional development, which in particular means a standard SQL-based DBMS.

While the equation may seem a little complex it has at least three solutions:

  • SQLite is the ideal candidate if you’re happy with the “single-file” model, you don’t have high concurrency constraints, and your only needs are interoperability (with Mac OS, Linux, Unix…), bigger storage and/or costs savings,
  • MySQL and PostgreSQL: if you need support for high-concurrency, really big storage (e.g. tens of GBs), advanced user-management, performance fine tuning and other advanced features you’ll have to jump out of the single-file world.
    If you don’t have specific requirements then MySQL and PostgreSQL will appear similar to you and equally do the job. However, in this kind of situation, I have a preference for MySQL, not because its inherent capabilities would be superior (as I’ve said MySQL and PostgreSQL are roughly equivalent for simple setups), but because, as the reference open-source DBMS for years, MySQL benefits from a huge community and toolbox. Moreover, while you’ll sure find the tools to work in good conditions with PostgreSQL, if you ever need to justify your choice to your hierarchy you’ll be in a better position if you choose the standard solution instead of the challenger.
    But as I’m not sectarian, and for completeness, I’ll cover both.

In this article I’ll quickly cover the setup of these three DBMS (with links to other resources for more extensive instructions) and illustrate their usage with a small VBA application, a revolutionary todo-list manager, that uses Access too.

Data schema

The data schema used by our application is really basic: one table with 3 columns:

  • Description: a textual description of the task,
  • Category: a tag that will help further categorization of the bunch of tasks you’ve created,
  • Due date: the limit date for accomplishing the task, after you could be in trouble with your boss!

Here is how it looks like in Access:

Access Tasks Table

Access Tasks Table

The equivalent in Access SQL dialect is:

CREATE TABLE Tasks
(
	Description	LONGTEXT,
	Category	VARCHAR(100),
	DueDate		DATETIME
);

MySQL

Installation

You can download the MSI installer from here: MySQL.
Once downloaded start it and accept any Windows security popup that could appear.
Then you can follow this slide-show for further instructions:

The list of products to install
Choose all, unless you're short in space
You can uncheck "Documentation"
Click "Next"
Click "Next"
Click "Next" to start the installation
Click "Next"
The list of prerequisites that will be installed
Click "Next"
Click "Next"
Accept the license agreement and click "Next"
Click "I Agree"
A sumup of the features that will be installed or updated on your computer
Click "Execute"
Click "Next"
Let the default location and click "Next"
Let the default location and click "Next"
Once installation is finished click "Next"
Choose a strong password, then click "Next"
Don't select anything and click "Install"
The products to configure
Click "Next"
Click "Next"
Select some additional icons then click "Next"
SQLite ODBC driver has been installed!
Click "Finish"
You can let default settings
Click "Next"
Click "Next"
Sumup of the operations, click "Next" to proceed with the installation
To check the installation launch the "ODBC Data Source Administrator"
Go to the "Drivers" tab and look for the SQLite driver entry
For security set a non trivial password for the root account
If you want to create another user click "Add User"
Otherwise click "Next"
Click "Next"
Wait for the installation to complete...
If you've chosen to add a user:
set username, password and choose a role
Click "OK"
Wait for the end of components installation...
Installation is finished, click "Finish" to launch SQLite Expert!
Unless you want to customize service click "Next"
Wait for the end of initial configuration...
You're done!
Click "Finish" and wait for MySQL Workbench to start.
You're done!
You can uncheck the option box and click "Finish"

and/or follow this video (directly go to 10:15 if you only want a quick introduction to MySQL Workbench):

Schema

In MySQL SQL dialect our schema creation query is:

CREATE TABLE Tasks
(
	Description	VARCHAR(1000),
	Category	VARCHAR(100),
	DueDate		DATETIME
);

Note that the “Description” field is using the limited “VARCHAR” type instead of the unlimited “TEXT” type because I’ve noticed some issue when retrieving this type of data using VBA.
Feel free to increase the maximum number of characters if 1000 seems too little for you (you can go up to more than 65000!).

PostgreSQL

Installation

PostgreSQL can be downloaded from the EnterpriseDB website.
To setup it quickly you can follow this slide-show:

The list of products to install
Choose all, unless you're short in space
You can uncheck "Documentation"
Click "Next"
Click "Next"
Click "Next" to start the installation
Click "Next"
The list of prerequisites that will be installed
Click "Next"
Click "Next"
Accept the license agreement and click "Next"
Click "I Agree"
A sumup of the features that will be installed or updated on your computer
Click "Execute"
Click "Next"
Let the default location and click "Next"
Let the default location and click "Next"
Once installation is finished click "Next"
Choose a strong password, then click "Next"
Don't select anything and click "Install"
The products to configure
Click "Next"
Click "Next"
Select some additional icons then click "Next"
SQLite ODBC driver has been installed!
Click "Finish"
You can let default settings
Click "Next"
Click "Next"
Sumup of the operations, click "Next" to proceed with the installation
To check the installation launch the "ODBC Data Source Administrator"
Go to the "Drivers" tab and look for the SQLite driver entry
For security set a non trivial password for the root account
If you want to create another user click "Add User"
Otherwise click "Next"
Click "Next"
Wait for the installation to complete...
If you've chosen to add a user:
set username, password and choose a role
Click "OK"
Wait for the end of components installation...
Installation is finished, click "Finish" to launch SQLite Expert!
Unless you want to customize service click "Next"
Wait for the end of initial configuration...
You're done!
Click "Finish" and wait for MySQL Workbench to start.
You're done!
You can uncheck the option box and click "Finish"

If you want more complete instructions along with a quick introduction to using pgAdmin (it starts at 03:40) you can watch this nice video tutorial:

Schema

In PostgreSQL SQL dialect our schema creation query is:

CREATE TABLE Tasks
(
	Description	VARCHAR(1000),
	Category	VARCHAR(100),
	DueDate		TIMESTAMP
);

Almost identical to MySQL except the name of the type representing a date and time: TIMESTAMP.
MySQL has a type named TIMESTAMP too but with a different meaning.

SQLite

Installation of SQLite Expert

As Access, an SQLite database does not use a dedicated server to work but is contained into a single file accessed through a standard API.
So contrary to MySQL and PostgreSQL you only need to setup a management tool.

I know two very good tools:

They’re both powerful and easy to use, but as SQLite Manager is delivered as a Firefox addon we’ll prefer the personal version of SQLite Expert (of course if you’re using it in a professional environment you should pay for the full version :)) as it is self-contained; but feel free to try SQLite Manager if you’re using Firefox, it’ll be even easier to setup.

The list of products to install
Choose all, unless you're short in space
You can uncheck "Documentation"
Click "Next"
Click "Next"
Click "Next" to start the installation
Click "Next"
The list of prerequisites that will be installed
Click "Next"
Click "Next"
Accept the license agreement and click "Next"
Click "I Agree"
A sumup of the features that will be installed or updated on your computer
Click "Execute"
Click "Next"
Let the default location and click "Next"
Let the default location and click "Next"
Once installation is finished click "Next"
Choose a strong password, then click "Next"
Don't select anything and click "Install"
The products to configure
Click "Next"
Click "Next"
Select some additional icons then click "Next"
SQLite ODBC driver has been installed!
Click "Finish"
You can let default settings
Click "Next"
Click "Next"
Sumup of the operations, click "Next" to proceed with the installation
To check the installation launch the "ODBC Data Source Administrator"
Go to the "Drivers" tab and look for the SQLite driver entry
For security set a non trivial password for the root account
If you want to create another user click "Add User"
Otherwise click "Next"
Click "Next"
Wait for the installation to complete...
If you've chosen to add a user:
set username, password and choose a role
Click "OK"
Wait for the end of components installation...
Installation is finished, click "Finish" to launch SQLite Expert!
Unless you want to customize service click "Next"
Wait for the end of initial configuration...
You're done!
Click "Finish" and wait for MySQL Workbench to start.
You're done!
You can uncheck the option box and click "Finish"

Here is a series of video tutorial from the SQLite Expert site that will show you how to:

  • create a new database:

  • create a new table with the designer:

  • create a new table using SQL (you can execute the SQL script presented above):

Installation of SQLite ODBC Driver

As for MySQL and PostgreSQL, to use your SQLite database VBA needs an ODBC driver, but while for MySQL and PostgreSQL their respective drivers are installed transparently along with the servers, the SQLite driver is only available as a separate package.
You can download this driver from the SQLite ODBC Driver web-site.

Then to install it follow the guide:

The list of products to install
Choose all, unless you're short in space
You can uncheck "Documentation"
Click "Next"
Click "Next"
Click "Next" to start the installation
Click "Next"
The list of prerequisites that will be installed
Click "Next"
Click "Next"
Accept the license agreement and click "Next"
Click "I Agree"
A sumup of the features that will be installed or updated on your computer
Click "Execute"
Click "Next"
Let the default location and click "Next"
Let the default location and click "Next"
Once installation is finished click "Next"
Choose a strong password, then click "Next"
Don't select anything and click "Install"
The products to configure
Click "Next"
Click "Next"
Select some additional icons then click "Next"
SQLite ODBC driver has been installed!
Click "Finish"
You can let default settings
Click "Next"
Click "Next"
Sumup of the operations, click "Next" to proceed with the installation
To check the installation launch the "ODBC Data Source Administrator"
Go to the "Drivers" tab and look for the SQLite driver entry
For security set a non trivial password for the root account
If you want to create another user click "Add User"
Otherwise click "Next"
Click "Next"
Wait for the installation to complete...
If you've chosen to add a user:
set username, password and choose a role
Click "OK"
Wait for the end of components installation...
Installation is finished, click "Finish" to launch SQLite Expert!
Unless you want to customize service click "Next"
Wait for the end of initial configuration...
You're done!
Click "Finish" and wait for MySQL Workbench to start.
You're done!
You can uncheck the option box and click "Finish"

To check the installation launch the “ODBC Data Source Administrator

[Thanks to Jeroen for mentioning this.]

On 64-bit systems there is 2 versions of the tool:

  • one for 64-bit applications: c:\windows\system32\odbcad32.exe, the default if you type odbcad32.exe in the CMD
  • one for 32-bit applications: c:\windows\syswow64\odbcad32.exe

As you are probably using a 32-bit version of Microsoft Office double-check you are correctly using the second one, the first one should show you far less drivers.

Go to the “Drivers” tab and look for the SQLite driver entry:SQLiteODBC_check_administrator

Schema

In SQLite SQL dialect our schema creation query is:

CREATE TABLE Tasks
(
	Description	VARCHAR(1000),
	Category	VARCHAR(100),
	DueDate		TIMESTAMP
);

This is the exact same SQL code as for MySQL.

The application

So, from here I assume you have correctly setup one or more DBMS, and that you are able to interact with them using their dedicated management application (MySQL Workbench, pgAdmin and SQLite Expert respectively).
Here comes the interesting part where we’ll use these DBMS to store and retrieve our data from an Excel/VBA application.

The user interface

The application is a basic task management system with a unique sheet that allows the user to see his current list of tasks and to update it before eventually saving it to the database.
Here is a screenshot of the application with my current todo-list:

Tasks Management Application Screenshot

Tasks Management Application Screenshot

There is four inputs (respectively two basic cells and two ActiveX TextBox with property PasswordChar set to “*“):

  • the source: what is the type of DBMS used; this will determine the information we need to provide and in which format for the connection string
  • the location: where to find the database; for MySQL and PostgreSQL that are server-based it is the address of their host (if this is the same machine you’re using to run the Excel application use “localhost“), for Access and SQLite that are file-based it is the path of the database file (the sample is able to interpret path relative to the current workbook location)
  • the user name and
  • the user password: if you’ve setup some user-based security you’ll need to provide them to interact with the database; note that the sample only takes them into account for server-based DBMS (MySQL and PostgreSQL)

Here are samples of configuration for each database:

Access MySQL PostgreSQL SQLite

For MySQL and PostgreSQL the login/password are the one you’d used to connect with MySQL Workbench and pgAdmin respectively.

Using the databases from VBA

The good news is that whatever the DBMS you’ve chosen you’ll use a common procedure to interact with it.
First you need to know which driver you’ll use to ensure communication between your VBA code and your DBMS. The role of the driver is to implement a standard API for a particular DBMS; this way, whatever the underlying DBMS, the code using it can communicate with it in a unified manner which among other advantages allows you to switch between different DBMS as your needs evolve.
For MySQL, PostgreSQL and SQLite I’ve used an ODBC driver, but not for Access as its ODBC driver is more limited and, among other things, does not support transactions which was a feature I wanted to use in the application for a cleaner update.

There is only one thing that will differ when using different DBMS: the connection-string, but once the connection is established all the other manipulations (retrieval and updates of data) are handled in a common way which avoid having to write one code by DBMS.
In the VBA code, the construction of the correct connection-string for and connection to a given source is managed by the “OpenConnection” method:

Private Function OpenConnection() As ADODB.connection
    ' Read type and location of the database, user login and password
    Dim source As String, location As String, user As String, password As String
    source = Range("Source").Value
    location = Range("Location").Value
    user = TasksSheet.UserInput.Value
    password = TasksSheet.PasswordInput.Value

    ' Handle relative path for the location of Access and SQLite database files
    If (source = "Access" Or source = "SQLite") And Not location Like "?:\*" Then
        location = ActiveWorkbook.Path & "\" & location
    End If

    ' Build the connection string depending on the source
    Dim connectionString As String
    Select Case source
        Case "Access"
            connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & location
        Case "MySQL"
            connectionString = "Driver={MySQL ODBC 5.2a Driver};Server=" & location & ";Database=test;UID=" & user & ";PWD=" & password
        Case "PostgreSQL"
            connectionString = "Driver={PostgreSQL ANSI};Server=" & location & ";Database=test;UID=" & user & ";PWD=" & password
        Case "SQLite"
            connectionString = "Driver={SQLite3 ODBC Driver};Database=" & location
    End Select

    ' Create and open a new connection to the selected source
    Set OpenConnection = New ADODB.connection
    Call OpenConnection.Open(connectionString)
End Function

This is the only part of the code that is not generic with regards to the underlying DBMS.
You could use it in any project potentially involving more than one source of data to uncouple the rest of the code from the trouble of building the connection-string and establishing the connection.

The remaining source code

The rest of the source code, in the module “mTasks“, contains the handlers for the click events on the buttons:

Public Sub LoadTasksButton_Click()
    Dim output As Range
    Set output = Range(TopLeft).Offset(1).Resize(1000, 3)

    output.ClearContents

    Dim connection As connection
    Set connection = OpenConnection()

    Dim result As ADODB.Recordset
    ' Load all the tasks from the database
    Set result = connection.Execute("SELECT description,category,dueDate FROM tasks")

    ' Insert them into the dedicated area
    Call output.CopyFromRecordset(result)

    connection.Close
End Sub

Public Sub UpdateTasksButton_Click()
    Dim connection As connection
    Set connection = OpenConnection()

    ' Create a record-set that holds all the tasks
    Dim records As ADODB.Recordset
    Set records = New ADODB.Recordset
    Call records.Open("SELECT description,category,dueDate FROM tasks", connection, , adLockOptimistic)

    ' Begin a transaction to avoid corrupting the database in case of error
    connection.BeginTrans

    ' Clean up the tasks list
    While Not records.EOF
        records.Delete
        records.MoveNext
    Wend

    ' If there is at least one task
    If Not IsEmpty(Range(TopLeft).Offset(1)) Then
        Dim dataRange As Range
        Set dataRange = Range(TopLeft).Offset(1).Resize(, 3)

        ' If there is more than one task
        If Not IsEmpty(Range(TopLeft).Offset(2)) Then
            Set dataRange = Range(Range(TopLeft).Offset(1), Range(TopLeft).Offset(1).End(xlDown)).Resize(, 3)
        End If

        Dim data As Variant
        data = dataRange.Value2

        Dim i As Integer
        For i = 1 To UBound(data)
            Dim row As Variant
            ' Extract ith row
            row = Application.WorksheetFunction.Index(data, i, 0)

            ' Add a new task
            records.AddNew
            records("Description") = row(1)
            records("Category") = row(2)
            records("DueDate") = CDate(row(3))
        Next i
    End If

    records.Save
    records.Close

    ' Commit all the work: cleanup + new tasks
    connection.CommitTrans

    connection.Close
End Sub

As you can see there is nothing specific to a given DBMS, just generic data plumbing code.
You don’t need (and don’t want :)) to know which DBMS you’re using, you just need to know it is compliant with some standards like SQL to interact with it.

Conclusion

As you’ve seen using a different DBMS than Access for your storage layer is a straightforward process that, if correctly implemented, won’t cost you a lot up front and could greatly enhance your applications and then your business if you fully leverage them to run it.

Later, if your database really becomes a critical asset for your business, holding tons of important data, with hundreds of requests per minute, high availability requirements, and that any shortage could represent a true operational risk you may want to (and really should) let a dedicated database-administrator (DBA) manage it, optimize it, and take care of all the tricky stuff.
Depending on your IT organization you could lose a lot in terms of flexibility, e.g. when you need to change a table structure or create a new one, but this is often the cost to pay for enhanced reliability and performance.

If you catch any typo or mistake, encounter any issue or have additional questions feel free to let a comment, I’ll do my best to answer in a timely manner.
Thanks for reading! 🙂

Useful resources

A more detailed tutorial on SQLite and SQL: SQLite Tutorial for Beginners: Learn in 3 Days

35 thoughts on “Using an Access, MySQL, PostgreSQL or SQLite database from VBA

  1. Thanks you for your tutorial! It’s really awsome! I’m writting an apps with Excel and Sqlite and it’s works with your explanations.
    Thanks

  2. Elegant solution!. Works fine with Access for me. Trying MySQL results in error messages. MySQL installation provided two drivers: ‘MySQL ODBC 5.2 ANSI Driver’ and’ ‘…Unicode… Using these drivers in the connectionString results in an errormsg stating that no standard driver is specified. (I am not an experienced user!). What goes wrong?
    Thanks,
    Harm

    • Hi Harm,
      could you please show the exact error message?
      This may be due to inconsistent version of Office and the driver: if you use a 64 bits OS with a 32 bits Office you probably have installed only the 64 bits versions of the MySQL drivers.
      Try to reinstall the MySQL drivers by taking care of installing the 32 bits version too.
      Hope this helps… 🙂

  3. Pingback: computer workbench

  4. First I wanna thank you for your tutorial, it seems to be realy easy to use once it’s instaled, but for some reason I just can’t instal the driver, I’m getting “Copy sqlite3odbc.dll to C:\Windows\system32\sqlite3odbc.dll failed” error every time I try, no matter if I run it as admin.
    Is any chanse you could help me?, please.

    • This is strange because no installer should try to copy stuff inside System32…

      But this really seems to be an issue with admin rights.
      Which error do you get if you try to manually copy the DLL using a CMD prompt running as administrator?

  5. Thank you for your clear tutorial. To make it even more complete:
    Use c:\windows\syswow64\odbcad32.exe to check if proper ODBC-driver is installed if you use 32-bits Office on a 64-bit windows system.

    • Thanks for your feedback Jeroen, I’ve updated the article to highlight this issue.
      Hopefully it will avoid some headaches to future readers. 🙂

  6. Hey pragmateek,
    A wonderful article and the best on that I have seen in the recent days. So clearly explained. Thank you.

    Can I encrypt SQLite DB and still connect from Excel?
    -Ram

    • Thanks for your feedback Ram.
      As for connecting to encrypted DB it all depends on the driver.
      AFAIK the one used in the article should handle this, but maybe not out-of-the-box so you might have to recompile a specific version.
      You should ask the driver’s author for more information, or just test it and check if it works…

  7. I Ge an error when i run..Microsoft visual basic runtime error ‘-2147467259(80004005)..Should we need to incude any references?

  8. I get the same error as above when I try to connect to a SQLite database file.
    The error occurs in the “OpenConnection()” function at the last line of code “Call OpenConnection.Open(connectionString)”.

    Regards,
    Jakob

    • I have found the issue.
      It seems that I had to install the SQLite3 ODBC Driver (32bit version)
      even though the 64bit version was installed.

      Maybe this can also help Santosh.

      • Glad you’ve fixed it by yourself.
        And yes the target driver bitness depends on the Office suite bitness: often 32-bit, even if Windows is 64-bit, for compatibility reasons. 🙂

  9. Hello
    tried to use your code and ran into Run-time error‘-2147467259(80004005) The # of IPD parameters 1 records.Delete
    records.MoveNext
    Wend

    Connection to database is OK, LoadTaskButton sub is working too

    Im using 64bit Excel 2016(office 356) and PostgreSQL Unicode(x64) driver, but getting same error when using PostgreSQL ANSI(x64), PostgreSQL ANSI driver from your code is not working for me as its 32bit version.

    Any idea where the problem might be and how to fix it?

    • *
      Run-time error‘-2147467259(80004005)
      The # of IPD parameters 1<6 the # of parameters markers

      Problem is on records.Delete part of code in UpdateTasksButton sub:
      While Not records.EOF
      records.Delete
      records.MoveNext
      Wend

      • Hello Martin,
        unfortunately 64-bit versions of Excel are not known for their reliability.
        I’ve had an nth issue with one of my user this week connecting to an Access database: we had to use the Microsoft Office 1X.0 Access Database Engine Object Library instead of the Microsoft DAO 3.6 Object Library.
        You could try to track the issue with Process Monitor though it can be quite cumbersome.
        Otherwise instead of using the Recordset API you could emit the equivalent SQL DELETE queries.

  10. Hello,
    thank you for reply, could you please provide me with some example how to do it? I am not familiar with how excel works with recordset and my attemts to clear recordset using SQL queries are not working.

    Another question, is there any way to use named table in SQL queries as if they were another database? I have found some lines that are supposed to be valid in putting data into Access database:

    dim exceltable as ListObject
    set exceltable = worksheets(“Sheet”).ListObject(“namedtable”)
    dim SQLstring as string
    SQLstring = “INSERT INTO database (ID, text_1, text_2) SELECT ID, text_1, text_2, FROM” & [Excel 12.0;HDR=Yes;Database=” & Application.ActiveWorkbook.FullName & “].” & getListObjectSQLAdress(exceltable)
    connection.execute SQLstring

    On communication with some test access database the SQL string was valid, but when i tried to use same approach to insert data into postgres DB i got “ERROR:syntax error at or near “[“; error while executing query” message.

    If there is no way to use table in excel itself, is there at least way to use recordset as one? Then i would be able to set up 2 database connections, one into excel, second into database and comunicate between them with recordset.

    • Hello Martin,
      as for the DELETE something like: connection.Execute("DELETE FROM tasks") before some INSERT.
      Concerning your seconde question I fear there is nothing automated and that you’ll have to write some plumbing to get the data into a RecordSet from one side with some SELECT and copy it to the other side with some INSERT.
      Maybe there is a way of switching a RecordSet‘s connection…

      • Hello,
        Thanks, thats not a method I am looking for as i need to delete only some data (i need to get only part of dataset and then upsert data back to DB), but currently i have some SQL workaround which wont use dataset in mind, I will share it when it will be finished.

        One more question – for my use i need to connect 2 tables which yields something around 20 collumns, but i need to add another approx. 10 collumns and change positions of collumns loaded from database. Is it possible (in LoadTask part) to output just some selected rows from dataset (say i need collumn 10 to A, columns 1-3 to B-D, 4-5 to G-H, etc)?

        • Hello again

          As to my question from yesterday, I have found a way that works:
          Do Until result.EOF
          Sheets(“Sheet1”).Cells(i, 1) = result.Fields(“ID”)
          Sheets(“Sheet1”).Cells(i, 7) = result.Fields(“var_1”)
          ‘and so on
          i = i + 1
          result.MoveNext
          Loop

          As for code for uploading back do database, with using just SQL i have finished my code for insert/update, is a bit too long for comment (79 lines from sub to end sub with spaces). If you are interested i can send you the code via e-mail, permission to put it in article, should you find it useful, included 🙂

          • Hi Martin,
            glad you’ve solved your issues. 🙂
            As for the code you can add a comment here with only the important parts like the SQL querying.

  11. Hi, ok, i reduced it a bit:

    Dim d As Integer, h as integer, IDstr As String, VALUESstr As String, ROWstr As String, tabulka As ListObject, list As Worksheet, x As Integer, SQLstr As String, SQLstr1 As String, SQLstr2 As String ' used variables
    Set list = Sheets("vbatabprop")   'sheet with table
    Set tabulka = list.ListObjects("vbatabulka")  'named table
    'now, define static part of SQL string
    SQLstr1 = "INSERT INTO test_table" & _
                " VALUES "
    SQLstr2 = " ON CONFLICT (id) DO UPDATE SET" & _
                " var_1 = EXCLUDED.var_1," & _ ' repeat this line for each collumn other than ID
                " where test_table.id = EXCLUDED.id" 'syntax may vary depending on used database, this is for PostgreSQL
    'now only thing we need is to construct string of upserted values:
    d = tabulka.Range.Rows.Count
    d = tabulka.Range.collums.Count 'define table dimension
    x = 1 'counter
    IDstr = ""
    VALUESstr = ""
    For i = 2 To d + 0 'assuming header is at row 1 in named table - first data on 2, 0 rows before named table.
        If InStr(1, IDstr, list.Cells(i, 1)) = 0 And list.Cells(i, 1)  "" Then
                IDstr = IDstr & list.Cells(i, 1) & ", "    'sometimes you need to select unique, for example if your data is joint of several tables. If not, keep only list.cells(i,1)""
            'For new rows without ID (when ID is handled in DB by autoincrement variable) separate INSERT is needed
         ROWstr = "'"
                For j = 1 To h
                    ROWstr = ROWstr & list.Cells(i, j) & "', '"                
                Next j 'using whole table. You can use several regions in either cycles or defined col number 
                ROWstr = Left(ROWstr, Len(ROWstr) - 3) 'string ends with "; '" needed on beginning of another column, need to get rid of those
        End If
        VALUESstr = VALUESstr & "(" & ROWstr & "); " ' create string for values
           If i / 500 = x Then
           VALUESstr = Left(VALUESstr, Len(VALUESstr) - 2)
           VALUESstr = Replace(VALUESstr, "''", "null") 'set empty cells as null to prevent errors
           SQLstr = SQLstr1 & VALUESstr & SQLstr2
           connection.BeginTrans
           connection.Execute SQLstr
           connection.CommitTrans
           x= x+1
           end if 
           'Its much faster and safer to chop data into smaller pieces. with 7000 rows 25 cols it went 3x faster than all at once, also there is limit to datapacket size in DB
    Next i
    VALUESstr = Left(VALUESstr, Len(VALUESstr) - 2)
    VALUESstr = Replace(VALUESstr, "''", "null")
    SQLstr = SQLstr1 & VALUESstr & SQLstr2
    connection.BeginTrans
    connection.Execute SQLstr
    connection.CommitTrans 'commit last part
    'check your data, you might have to use different characters, for example if your system uses , as decimal separator or if there are ' in inserted values. Use different separators in code and replace them at the end, same process as with null values. You can also run into problems with different timestamp variables, using .cells(r,c) gives hh:mm:ss dd.mm.yyyy format (at least in my excel) for any datetime formating used in your excel table, whereas DB needs yyyy.mm.dd hh:mm:ss. (may vary) I suggest using cells(r,c).text for datetime cells and use compatible format in excel table (or set compatible format for those columns at the beginning of macro)
  12. Hi. Thank you for your tuto. I’ve copied the code and tried to test it. LoadTasks function is working fine but I got this error while running UpdateTasks function :”request-based deletion failed because the line to be deleted was not found” at the line “records.Remove” . I make some searches on google but I don’t find anything that could help.

    • Hi,
      sorry for the late answer, I’ve missed the notification.
      Which database are you using ?
      Are you sure the data have been correctly saved ?

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove me you\'re human :) *