The Linux Page
Home

CREATE TABLE + DECIMAL in MS-Access VBA code

Submitted by on Thu, 05/28/2009 - 03:43

MS-Access defines many different types, including a newer one called DECIMAL.

You are supposed to be able to create tables with the DECIMAL type. So a statement such as:

   CREATE TABLE MyTbl (decimal_test DECIMAL(20, 5))

should work. Right?

I have been using CurrentDb.Execute for a while and that statement was causing the Execute function to return an error saying that the field definition was erronous (Syntax error in field definition, to be exact).

The trick is very simple!

You need to use the following instead of CurrentDb.Execute:

   CurrentProject.Connection.Execute "<sql statement>"

So? Two different ways to access the same Database, both of which have different capabilities?!

Wow!

Re: CREATE TABLE + DECIMAL in MS-Access VBA code

Submitted by Francesco Giossi (not verified) on Wed, 02/24/2021 - 08:41.

11 years later, here is my answer

CurrentDB is an object coming from the very old DAO (Database Access Object).
CurrentProject is an object related with ADO (Activex Data Object).

From the web: DAO is the native data access method for the Jet (Ms-Access) data tables. ADO "Active X Data Objects" is an industry friendly connection to almost all types of database.

With DAO you have an object, with ADO you have a connection.

Basically DAO is still valid and it's in Access because of legacy reasons (allows compatibility with all the access versions)