I&#39;m trying to write a query that extracts and transforms data from a table and then insert those data into another table. Yes, this is a data warehousing query and I&#39;m doing it in MS Access. So basically I want some query like this:

    INSERT INTO Table2(LongIntColumn2, CurrencyColumn2) VALUES
      (SELECT LongIntColumn1, Avg(CurrencyColumn) as CurrencyColumn1 FROM Table1 GROUP BY LongIntColumn1);

I tried but get a syntax error message.

What would you do if you want to do this?

How to do INSERT into a table records extracted from another table

I&#39;m involved with updating an Access solution.  It has a good amount of VBA, a number of queries, a small amount of tables, and a few forms for data entry &amp; report generation.  It&#39;s an ideal candidate for Access.

I want to make changes to the table design, the VBA, the queries, and the forms.  How can I track my changes with version control? (we use Subversion, but this goes for any flavor) I can stick the entire mdb in subversion, but that will be storing a binary file, and I won&#39;t be able to tell that I just changed one line of VBA code.

I thought about copying the VBA code to separate files, and saving those, but I could see those quickly getting out of sync with what&#39;s in the database.

How do you use version control with Access development?

I want to manipulate a Microsoft Access database (.accdb or .mdb file) from my Java project. I don&#39;t want to use the JDBC-ODBC Bridge and the Access ODBC driver from Microsoft because:

- the JDBC-ODBC Bridge has been removed from Java SE 8 and is not supported (ref: [here][1]),
- the JDBC-ODBC Bridge does not work properly with the Access ODBC driver when text includes Unicode characters with code points above U+00FF (ref: [here][3]), so such a setup would not be able to handle characters such as Greek, Russian, Chinese, Arabic, etc.,
- the Access ODBC driver from Microsoft only works in Windows, and 
- there are separate 32-bit and 64-bit versions of the Access Database Engine (and ODBC driver) which can be a nuisance for deployment.

I have seen other answers mentioning a JDBC driver for Access databases named [UCanAccess][4]. How can I set up my Java project to use this approach?

(Answers suggesting better ways of working with Access databases from Java would also be most welcome.)

[1]: http://docs.oracle.com/javase/7/docs/technotes/guides/jdbc/bridge.html
[2]: http://en.wikipedia.org/wiki/Basic_Multilingual_Plane
[3]: https://stackoverflow.com/a/20982205/2144390
[4]: http://ucanaccess.sourceforge.net/site.html


Manipulating an Access database from Java without ODBC

I need to get the total of two fields which are within the same row and input that number in a field at the end of that same row.

This is my code. 

    Sum(tbl1.fld1 + tbl1.fld2) AS [Total]

Is this what the SUM function is used for, or can you only use the SUM function for getting the total of a column?

Thanks

How to SUM two fields within an SQL query

Consider the need to develop a lightweight desktop DB application on the Microsoft platforms.

It could be done fairly easily with MS Access but I&#39;d like to be able to distribute it to others and I don&#39;t want to pay for a runtime license.  

Requirements:

  -  easy distribution to others
  -  no runtime licensing issues

Considerations and Candidates:

  -  [Base from the OpenOffice suite][1]. My concerns were around its stability. 
  -  [MySQL][2] + writing custom DB code in C++ or Python or whatever seems like a rather heavy-handed solution.  

**Question**: What are the low cost or free database alternatives to MS Access?


----------

**See Also:** [Open Source Reporting Engines][3]

----



  [1]: http://en.wikipedia.org/wiki/OpenOffice.org_Base
  [2]: http://dev.mysql.com/downloads/
  [3]: https://stackoverflow.com/questions/126622/open-source-reporting-engines

---

@Schnapple

Bruceatk kind of hit on what I&#39;m thinking of; it&#39;s not so much the DB engine as I want the other niceties that Access brings to the party.  The nice form designer, the nice reporting engine etc.  But you do raise a very good point about the installation footprint.  I had considered that but I&#39;ve not made any firm decisions about which way I&#39;m going with this yet anyway.  It&#39;ll probably be something fairly lightweight anyway and a small installation footprint would definitely be a plus.


---

@Remou,

No I was unaware that the MS Access 2007 runtime is free; thanks for pointing that out.  The last time I&#39;d bothered to investigate it (I don&#39;t remember when it was) I think it was a fairly expensive license for the runtime because I think they were trying to sell it to Corporate IT departments.

And thanks to everyone else who responded as well; I was completely unaware of those other options you all pointed out.



Good Free Alternative To MS Access

I have a global variable that is an instance of my custom class.

How do I check if the object is set or if I need to initialize it?



VBA check if object is set

what&#39;s faster?

    DELETE FROM table_name;

or

    DELETE FROM table_name where 1=1;

why?

does `truncate table` work in access?

delete all from table

How can one programmatically sort a union query when pulling data from two tables? For example,

    SELECT table1.field1 FROM table1 ORDER BY table1.field1
    UNION
    SELECT table2.field1 FROM table2 ORDER BY table2.field1

Throws an exception

Note: this is being attempted on MS Access Jet database engine



SQL Query - Using Order By in UNION

Since installing [the windows update for Office 2010 resolving KB 4484127](https://support.microsoft.com/en-us/help/4484127/security-update-for-office-2010-november-12-2019) I get an error while executing queries which contain a WHERE clause.

For example executing this query:
                                                                              
    DoCmd.RunSQL &quot;update users set uname= &#39;bob&#39; where usercode=1&quot;

Results in this error:

&gt; Error number = 3340 Query &#39; &#39; is corrupt

The [update in question](https://support.microsoft.com/en-us/help/20191112/security-update-deployment-information-november-12-2019) is currently still installed:

[![Screenshot showing Microsoft Office 2010 Service Pack 2 update 448127][1]][1]

How can I successfully run my queries? Should I just uninstall this update?

  [1]: https://i.stack.imgur.com/V5hEf.png



Getting Error 3340 Query &#39; &#39; is corrupt while executing queries DoCmd.RunSQL

Is it possible to create a Dump of SQL commands from a Microsoft Access database? I hope to convert this MDB file into a MySQL database for importing so I don&#39;t have to go through the CSV step.

I would expect even an MSSQL dump file to still contain workable SQL commands, but I know nothing of MSSQL, please let me know.

How can I convert an MDB (Access) file to MySQL (or plain SQL file)?

When I had only one inner join in my SQL statement, it worked perfectly. I tried joining a second table, and now I am getting an error that says there is a syntax error (missing operator). What is wrong here?

    adsFormView.SelectCommand = &quot;SELECT * FROM [tableCourse] INNER JOIN [tableGrade] ON [tableCourse].[grading] = [tableGrade].[id] INNER JOIN [tableCourseType] ON [tableCourse].[course_type] = [tableCourseType].[id] WHERE [prefix]=&#39;&quot; &amp; myPrefix &amp; &quot;&#39; AND [course_number]=&#39;&quot; &amp; myCourseNum &amp; &quot;&#39;&quot;

SQL multiple join statement

I&#39;m trying to get the length of an array, yet I keep getting this error:

&gt; Object required

Am I doing something wrong?

    Dim columns As Variant
    columns = Array( _
    &quot;A&quot;, &quot;ID&quot;, _
    &quot;D&quot;, &quot;Name&quot;)
    Debug.Print columns.Length  &#39; Error: Object required

Get length of array?

I use the following code in Python (with pyodbc for a MS-Access base).

    cursor.execute(&quot;select a from tbl where b=? and c=?&quot;, (x, y))
It&#39;s Ok but, for maintenance purposes, I need to know the complete and exact SQL string send to the database.  
Is it possible and how ?

How to see the real SQL query in Python cursor.execute using pyodbc and MS-Access

I have a Visual Studio 2008 solution with two projects (a Word-Template project and a VB.Net console application for testing).  Both projects reference a database project which opens a connection to an MS-Access 2007 database file and have references to System.Data.OleDb.  In the database project I have a function which retrieves a data table as follows

     private class AdminDatabase
       &#39; stores the connection string which is set in the New() method
       dim strAdminConnection as string

       public sub New()
       ...
       adminName = dlgopen.FileName
       conAdminDB = New OleDbConnection
       conAdminDB.ConnectionString = &quot;Data Source=&#39;&quot; + adminName + &quot;&#39;;&quot; + _
           &quot;Provider=Microsoft.ACE.OLEDB.12.0&quot;

       &#39; store the connection string in strAdminConnection
       strAdminConnection = conAdminDB.ConnectionString.ToString()
       My.Settings.SetUserOverride(&quot;AdminConnectionString&quot;, strAdminConnection)
       ...
       End Sub

       &#39; retrieves data from the database
       Public Function getDataTable(ByVal sqlStatement As String) As DataTable
            Dim ds As New DataSet
            Dim dt As New DataTable
            Dim da As New OleDbDataAdapter
            Dim localCon As New OleDbConnection
    

            localCon.ConnectionString = strAdminConnection

            Using localCon
                Dim command As OleDbCommand = localCon.CreateCommand()
                command.CommandText = sqlStatement
                localCon.Open()
                da.SelectCommand = command
                da.Fill(dt)
                getDataTable = dt
            End Using
    
        End Function
    End Class


When I call this function from my Word 2007 Template project everything works fine; no errors. But when I run it from the console application it throws the following exception

&gt; ex = {&quot;The &#39;Microsoft.ACE.OLEDB.12.0&#39;
&gt; provider is not registered on the
&gt; local machine.&quot;}

Both projects have the same reference and the console application did work when I first wrote it (a while ago) but now it has stopped work.  I must be missing something but I don&#39;t know what.  Any ideas?


Microsoft.ACE.OLEDB.12.0 provider is not registered

How do I determine whether an object is a member of a collection in VBA?
  
Specifically, I need to find out whether a table definition is a member of the `TableDefs` collection.

Determining whether an object is a member of a collection in VBA

I&#39;ve a table contains the columns like

      Prefix    |  CR
    ----------------------------------------
      g         |  ;#WR_1;#WR_2;#WR_3;#WR_4;# 
      v         |  ;#WR_3;#WR_4;#
      j         |  WR_2
      m         |  WR_1
      d	        |  ;#WR_3;#WR_4;#   
      f9        |  WR_3

 
I want to retrieve data from **CR** column WHERE it has the longest text string i.e in current table it is **;#WR_1;#WR_2;#WR_3;#WR_4;#**.
I&#39;m using 

    SELECT max(len(CR)) AS Max_Length_String FROM table1 
But it retuns 
                           
    Max_Length_String
    ----------------------------------------
    26
But what i need is not the length (26), i wanted like this 
 
    Max_Length_String
    ----------------------------------------
    ;#WR_1;#WR_2;#WR_3;#WR_4;# 

             

How to find longest string in the table column data

For the properties:

    Persist Security Info=true

and

    Persist Security Info=false

Can you tell me what is the difference between them, and if I don&#39;t put it in my connection what will happen?

    connect.ConnectionString = &quot;Provider=Microsoft.ACE.OLEDB.12.0;
                                Data Source=C:/Users/Nourah/Downloads 
                                /Phase3/Salary.accdb; 
                                Persist Security Info=False;&quot;;

Persist Security Info Property=true and Persist Security Info Property=false

So here&#39;s the bug: In a **64-Bit** VBA host (for example Access 365 64-bit or Excel 2016 64-bit) create a class module `SomeClass`:

```lang-vb
&#39; this needs to be here to trigger the bug: 
Private Sub Class_Terminate()
End Sub
```

and then some module `Test`: 

```lang-vb
Function ReturnFalse(o As Object) As Boolean
    ReturnFalse = False
End Function

Sub Test()
    Debug.Print ReturnFalse(New SomeClass)
    If ReturnFalse(New SomeClass) Then
        Debug.Print True
    Else
        Debug.Print False
    End If    
End Sub
```

Now, if you are using a **32-bit** VBA host and run &quot;Test&quot; in the immediate window, the the expected result shows up: 

```
False
False
```

However, if you are using a **64-bit** VBA host, then this comes out: 

```
False
True
```

Except, when you remove or rename the `Class_Terminate()` sub, in which case the correct output appears. 

I&#39;ve tracked the bug down to this minimal example. Apparently, the problem seems to be, that using a temporary object (`new SomeClass` here) breaks the evaluation of the `IF` condition somehow, making it appear that the value of the condition is `True` not matter what. 

OK, that&#39;s a serious bug, because the 64 bit compiler is bonkers and all `IF` are in trouble. 

All `IF`? what about `WHILE`?

    While ReturnFalse(New SomeClass)
     Debug.Print &quot;Oh no!&quot;
    Wend

Yes, `WHILE` is also in trouble, because this prints &quot;Oh No!&quot; in a loop.

That&#39;s a lot of trouble and I could reproduce it everywhere: 

* Microsoft&#174; Access&#174; f&#252;r Microsoft 365 MSO (16.0.14026.20294) 64-Bit 
* Microsoft Access 2016 MSO (16.0.9029.2167) 64-Bit
* Microsoft Access 2013 (15.0.4420.1017) MSO (15.0.4420.1017) 64-Bit

..And also in **Excel** of course. 

Summary: I could find this bug in all versions of Office I have, starting from 2013 and it is probably at least 8 years old. 

Ok, did this bug affect other people before? Yes: 

This post last year: 

https://stackoverflow.com/questions/65041832/vba-takes-wrong-branch-at-if-statement-severe-compiler-bug/68032045#68032045

This post in excel.uservoice.com (which apparently is Microsoft&#39;s user suggestion box or something) from *October 2018*: 

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/35735881-fix-inlined-member-calls-on-user-objects-on-64-bi

Allright, so let&#39;s file a bug report. 

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_mac-mso_mac2016/how-do-i-report-vba-bugs/bb4e5dea-9996-4232-9b5b-7dd57f76736c

&gt; If, after testing with others, the code fails and it really shouldn&#39;t, you can report the issue directly to Microsoft using the Smile button in Excel.

What? 

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_2016/excel-2016-vba-bug/b799dfc2-7cef-417d-8a41-96661a360c43

&gt; 1. Open Excel &gt; File &gt; Feedback &gt; Send a Frown
&gt; 2. Through Uservoice - Click the following link to see the feedback of others and to provide feedback - https://excel.uservoice.com/

This is not a suggestion for a new icon color scheme. It is an 8 year old bug, which makes Access apps and Excel sheets with macros compute wrong answers (and also it blocks a migration to Office 64 here, because we can not get our code out). 

Now here are my question:

* How can I get this bug fixed? 
* Is someone with a reasonably impressive SLA or better connections here who would support my request? 
* Is there a way to report VBA bugs directly? (This, because we currently suspect more bugs in the 64-bit VBA)
* I&#39;ve made a new report in uservoice. Do you think it could be voted up? https://access.uservoice.com/forums/319956-access-desktop-application/suggestions/43660329-fix-this-64-bit-vba-compiler-bug-temporary-object

**UPDATE**: x-posted to

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-msoffice_custom-mso_2019/invalid-code-by-vba-64-bit-compiler/b91f984a-194c-4453-b8c5-02881afaf83b

**UPDATE 2**:

I had the chance to try the code on a Office 365 for Mac installation (where Win64 is defined as `true`) and *the bug does not show up there*. So it&#39;s a PC thing right now.

**UPDATE 3**: 

Post made it to HN and The Register: 

https://www.theregister.com/2021/08/19/64_bit_microsoft_vba_bug/
https://news.ycombinator.com/item?id=28188251

**UPDATE 4**: 

Just checked today (2021-11-15) against Office 365 and the ***bug ist gone now***! Looks like somebody payed attention. I can&#39;t figure out however, which of the gazillion cumulative updates I received this year did the trick and don&#39;t know yet, if the other Office versions are fixed too. 


How can I get this 8 year old VBA 64-bit compiler bug fixed?

    Sub btn1_Click()
    Static value As Integer
    value = value + 1
    MsgBox value
    End Sub

I swear when I was taking a VB.net course in college there was a shorter way to tell a variable to add &#39;&#39; to itself. Maybe x=+1. I am using Access now though instead of visual studio. When I try that within the VBE it removes the +. I even removed `Option Explicit` with no change

Assuming the answer will be no, there is no way to short-hand it &amp; its just a peculiarly of VBA

VBA shorthand for x=x+1?



It has been a good number of years since I did some programming with Classic ASP and Microsoft Access where we used &quot;Microsoft.Jet.Oledb&quot; driver to access and display the data. 

I have been asked to do some work with accessing MS Excel data using &quot;Microsoft.Ace.Oledb&quot;. I have found this to be part of the &quot;Microsoft Access 2010 Engine Redistributable&quot; download. 

I would like to know if &quot;Microsoft.Jet.OleDb&quot; has replaced &quot;Microsoft.Ace.Oledb&quot; driver and are essentially the same or are they completely different things? 

Also, do you normally get the &quot;Microsoft.Ace.Oledb&quot; driver when you buy MS Access 2010?


Difference between Microsoft.Jet.OleDb and Microsoft.Ace.OleDb

What is the best way to perform bulk inserts into an MS Access database from .NET?  Using ADO.NET, it is taking way over an hour to write out a large dataset.

*Note that my original post, before I &quot;refactored&quot; it, had both the question and answer in the question part.  I took Igor Turman&#39;s suggestion and re-wrote it in two parts - the question above and followed by my answer.*

Writing large number of records (bulk insert) to Access in .NET/C#

When I tried to connect R with Access database I get an error 

    odbcConnectAccess is only usable with 32-bit Windows

 

Does anyone has an idea how to solve this?


    library(RODBC) 
    mdbConnect&lt;-odbcConnectAccess(&quot;D:/SampleDB1/sampleDB1.mdb&quot;)



How to connect R with Access database in 64-bit Window?

Can someone point me in the right direction on how to open a .mdb file in python? I normally like including some code to start off a discussion, but I don&#39;t know where to start. I work with mysql a fair bit with python. I was wondering if there is a way to work with .mdb files in a similar way?

  

how to deal with .mdb access files with python

Syntax Error (missing Operator) in query expression &#39;tbl_employee.emp_id = tbl_netpay.emp_id INNER JOIN tbl_gross ON tbl_employee.emp_id = tbl_gross.emp_ID INNER JOIN tbl_tax ON tbl_employee.emp_id - tbl_tax.emp_ID&#39;.


    SELECT tbl_employee.emp_ID,
    tbl_employee.emp_name,
    tbl_gross.BasicSalary,
    tbl_gross.totalOT,
    tbl_netpay.totalGross,
    tbl_tax.totalLate,
    tbl_tax.allowance,
    tbl_tax.SSS,
    tbl_tax.PhilHealth,
    tbl_tax.GSIS,
    tbl_tax.HDMF,
    tbl_netpay.totalDeduc,
    tbl_netpay.emp_ti,
    tbl_netpay.emp_wt,
    tbl_netpay.emp_np
    FROM  tbl_employee
    INNER JOIN tbl_netpay ON tbl_employee.emp_id = tbl_netpay.emp_id
    INNER JOIN tbl_gross ON tbl_employee.emp_id = tbl_gross.emp_ID
    INNER JOIN tbl_tax ON tbl_employee.emp_id = tbl_tax.emp_ID;

I always get the error above.

Multiple INNER JOIN SQL ACCESS

I have a query which includes fields named *openingbalance* and *commissions*. I would like to compute values for *commissions* based on *openingbalance*, similar to this `Select Case` block in Access VBA:

    Select Case OpeningBalance
       Case 0 To 5000
            commission = 20
       Case 5001 To 10000
            commission = 30
       Case 10001 To 20000
            commission = 40
       Case Else
            commission = 50
    End Select

But since Access doesn&#39;t allow `Select Case` in a query, how can I accomplish my goal in Access SQL?

All Ms Access Solutions on Gang of Coders

Total of 25 Ms Access Solutions