Access database engine not enabled by default? (1 Viewer)

[ysdai]

Hi,

I'm not a professional developer, just someone who knows some VBA and SQL, with just enough database knowledge to put some things together.

I'm currently working on an Access (Office365 64-bit) front end that uses linked tables stored in a MySQL server on Ubuntu (via ODBC drivers).
This is the second one I'm developing (Project 2), the first one (Project 1) also has linked tables on Ubuntu-MySQL.
However, there are some issues in Project 2 that I didn't encounter in the Project 1.

First of all, the DAO objects were unavailable (DAO.Database, DAO.Recordset, etc) until I manually made a reference to "Microsoft Office 16.0 Access database object." In Project 1, they were there by default, and the DAO reference was checked by default.

Then I was having trouble filtering my subform through controls on the main form, which I could easily accomplish in Project 1. After screwing around for hours, I found that the SQL syntax on Project 2 is different. The wildcard character, which is "*" in Access SQL, became "%", like in MySQL. I'm not very good at the language so I'm not sure if it's using the SQL Server syntax or that of MySQL, but it's no longer the same as in Project 1.

I created both projects using the same computer, on the same installation of MS Office. But the preloaded stuff are different. I'm assuming that Project 2 is not running on the default Access database engine? I was able to work around all the issues that arose until now, but I really have no idea what actually happened. Can someone shed a light on this?

 

[CJ_London]

difficult to advise without seeing things like your connections strings, the type of sql for your form recordsources, your odbc drivers for example.

If you are using DAO then you would use *. For sql server and mySQL you use %.

One thing to check is if in the access properties you have set syntax to ansi 92 (File>Options>Object Designers). If you have then DAO will use % as well - perhaps it is checked in the first project and not the second?

The sql syntax used in sql server and mysql are similar but not the same - each has their own variations. DAO sql syntax will be different again

 

[ysdai]

difficult to advise without seeing things like your connections strings, the type of sql for your form recordsources, your odbc drivers for example.
...

Thanks for the reply.

I did not use connection strings. To use them, maybe I'd need to use ADODB, that's something I'd like to try out on my next project.

What I did was, I downloaded and installed the ODBC Driver for MySQL from their website, then in the ODBC Data Source applet in Control Panel, I created a DSN, in which I specify the server address, username, and the name of the database I will connect to. Then in Access I went to [External Data] > [New data source] > [From other sources] > [ODBC] and point to the DSN I created. If the connection is successful, I will be shown a list of tables I can link to or import. I chose the link option.

I just went to check the SQL setting in Options, and it was indeed set to ANSI-92. Funny thing is, I didn't configure that; I never knew it was there until you told me. As for my SQL filter queries, I have a subform in datasheet view, then I'd have maybe a textbox on my main form, and in the [Change] event I would use something like

Me.Subform.Form.Recordsource="SELECT * FROM Table1 WHERE Field1 Like '*" & Me.Textbox.Text & "*';"

so that I can filter as I type. I usually have multiple fields to filter simultaneously. As you can see. I used the "*" wildcard, that was why my search queries in Project 2 failed.

Still unclear why my default settings changed, SQL syntax, default references, etc. But I hope I cleared some things up.

 

[Ranman256]

yes, add DAO to the reference, then you can connect all day long.

 

[ysdai]

yes, add DAO to the reference, then you can connect all day long.

Yes I can. But it would be quite a pain if I have to add that reference on every single computer that's going to use my program. I'm trying to figure out why DAO wasn't there by default, as it used to be, and why the change of SQL syntax without my intervention. I wanted to know what's behind all these so that maybe I can have better control over my next project.

 

[theDBguy]

Hi. Did you create project 1 from scratch but created project 2 from an existing db? If you create a blank database now, is the database engine not selected?

 

[Pat Hartman]

Create a new database. Open it and uncheck the Use ANSI 92 option. Then import all your objects.

I don't know what the Use ANSI 92 does exactly, all I know is that it breaks existing SQL so I've never used it.

 

[CJ_London]

if you really want to know - this link may help
https://flylib.com/books/en/2.401.1.265/1/

 

[isladogs]

Yes I can. But it would be quite a pain if I have to add that reference on every single computer that's going to use my program. I'm trying to figure out why DAO wasn't there by default, as it used to be, and why the change of SQL syntax without my intervention. I wanted to know what's behind all these so that maybe I can have better control over my next project.

The old DAO reference was superceded in A2007 by the Microsoft Office xx.0 Access database object library which has additional functionality.
You don't need & cannot install the old DAO reference as well as it would conflict with the newer reference which is installed by default.

The ANSI 92 option is switched OFF by default. It will only get switched on if someone ticks that option.
Easy to do and then forget about if you aren't aware what it does ...as I did many years ago....
One way of knowing when it is switched on... entering the Like operator for wildcard searches will get changed to ALike.

 

[ysdai]

Hi. Did you create project 1 from scratch but created project 2 from an existing db? If you create a blank database now, is the database engine not selected?

I created both projects from scratch. Project 1 started as Access database and then was migrated to MySQL. I was going to base Project 2 on Project1 but there was too much work, so it was created fresh and linked to pre-created tables in MySQL. Project2 never has any Access native tables in it, I'm not sure if this has anything to do with DAO not being available by default.

 

[ysdai]

CJ_London:

I don't know why I could not reply to your last post with a quote. The system wouldn't let me.
Anyway, That is very interesting stuff. Thanks a lot!

 

[theDBguy]

I created both projects from scratch. Project 1 started as Access database and then was migrated to MySQL. I was going to base Project 2 on Project1 but there was too much work, so it was created fresh and linked to pre-created tables in MySQL. Project2 never has any Access native tables in it, I'm not sure if this has anything to do with DAO not being available by default.

Did you try what I said though? Create a blank database and then immediately check the references to see if the Database Engine is included or not. Just curious...

 

[ysdai]

The old DAO reference was superceded in A2007 by the Microsoft Office xx.0 Access database object library which has additional functionality.
You don't need & cannot install the old DAO reference as well as it would conflict with the newer reference which is installed by default.

I started using Access in version 2013, and the computers in the office are all 2013+, so that shouldn't be a problem. I can probably get by with a startup script that adds DAO reference if it's not there.

The ANSI 92 option is switched OFF by default. It will only get switched on if someone ticks that option.
Easy to do and then forget about if you aren't aware what it does ...as I did many years ago....
One way of knowing when it is switched on... entering the Like operator for wildcard searches will get changed to ALike.

Yes, that's exactly what appeared when I used the Like operator. How it got switched to ANSI-92 is still a mystery, maybe I did it without knowing, but I don't care now. I'm going to give it a try.

 

[ysdai]

Did you try what I said though? Create a blank database and then immediately check the references to see if the Database Engine is included or not. Just curious...

Yes, I just did. A fresh blank database with nothing in it. I went to check the references immediately. And yes, DAO was checked by default.
But it wasn't the case with my Project2. I had to Google out the full name of DAO and find it in the references list.

 

[theDBguy]

Yes, I just did. A fresh blank database with nothing in it. I went to check the references immediately. And yes, DAO was checked by default.
But it wasn't the case with my Project2. I had to Google out the full name of DAO and find it in the references list.

Hi. That's what I would expect, so I don't think you need a script to add it to your projects. Whatever happened to Project 2 that removed the DAO reference was probably just an accident.

 

[isladogs]

I started using Access in version 2013, and the computers in the office are all 2013+, so that shouldn't be a problem. I can probably get by with a startup script that adds DAO reference if it's not there.

To reiterate, you don't need the DAO reference and if you try to add it you will get a conflict as the DAO code is already included in the Microsoft Office xx.0 Access database object library

A fresh blank database with nothing in it. I went to check the references immediately. And yes, DAO was checked by default.

Did you create an MDB database? If so, that will use the old DAO reference

 

[ysdai]

To reiterate, you don't need the DAO reference and if you try to add it you will get a conflict as the DAO code is already included in the Microsoft Office xx.0 Access database object library

DAO was indeed included in the list of references, but with my current project, it wasn't checked by default, when it should be.
The statement Dim rs As DAO.Recordset threw out a "User-defined type not defined" error, and it took me some googling to correct it. I didn't run into this problem with any one of my previous projects since Access 2013, that statement simply worked out of the box. And that was why I never knew the full name of the DAO reference as I never had to add it manually.

Did you create an MDB database? If so, that will use the old DAO reference

Now it's ACCDB by default.

 

[ysdai]

Whatever happened to Project 2 that removed the DAO reference was probably just an accident.

Yeah, probably, same goes for the SQL. Guess I'll never find out.
Thanks, people, for all your help. If something unexpected comes up again, I'll put it in another thread.