0

I just signed up with stackoverflow and this is my first question. I am working on my first Access project. Although I have some proficiency in using VBA in Excel, it is really just a general programming knowledge and searching the web for someone else to give me the syntax. I still haven't completely figured out how objects, methods, etc. all work.

We have data stored in a proprietary format that we can save as CSV. The data contains data points based on time. I was able do what needed to be done in Excel but that fell through when we exceeded the million plus rows. Access handles the task easily but now I need to create some code to import the data into tables with relationships (done), create queries (stuck here), allow the user to select a time range and export the data in the time range to an Excel spreadsheet (I should be able to two the latter two).

As I have always done with Excel, I've looked for the correct syntax on the web but everything seems to use the DAO reference. Doing somethings as declaring a database using DIM db as DAO.Database fails with Compile error: User-defined type not defined. I've read that the Microsoft Access 15.0 Object Library should run all the Microsoft DAO 3.6 Object Library code but not for me.

So after this long-winded explanation, I could use the answer to either of two questions.

  1. Is there something I am doing wrong with the references or coding that would allow me to use CreateQueryDef and others?
  2. Is there another way to create a query without using DAO tools?

This is my first question ever on any forum. I've always managed to find a solution I could make work. Hope I haven't broken too many "How to post a question" rules. Any help with this would be greatly appreciated.

Thanks a bunch.

ToniP

Erik A
  • 31,639
  • 12
  • 42
  • 67
ToniP
  • 3
  • 2
  • I'll answer your second question: yes. DAO is "ancient." It was replaced by ADO about 20 years ago. To learn how to use ADO (which I recommend), start [here](https://learn.microsoft.com/en-us/sql/ado/guide/appendixes/using-ado-with-microsoft-visual-basic). To create queries, read up on the ADO Command object [here](https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/command-object-ado). – BobRodes Nov 07 '17 at 02:36
  • 1
    @BobRodes If you want to post new facts how DAO is ancient or wrong, you can do so [here](https://stackoverflow.com/questions/9737099/difference-between-ado-and-dao). Since Office 2007, DAO can do more than ADO in Access, such as working with the Attachment field type (which probably should be avoided, but well). Imho you should use it in Access since it's native to access, and shouldn't use it outside access – Erik A Nov 07 '17 at 11:02
  • See Gustav's answer below as well. My answer is apparently quite a bit out of date. – BobRodes Nov 07 '17 at 21:54

2 Answers2

2

Bob is off track. An attempt to push ADO in Access was made about 15 years ago, but with little success.

Thus, from Access 2007, DAO was again the default and preferred engine.

However, it is was renamed, and that is the Reference you may be missing:

Microsoft Office xx.0 Access database engine Object Library

In your code, it still is DAO.

Gustav
  • 53,498
  • 7
  • 29
  • 55
  • Sorry for that, and thanks for letting me know. I guess I was one of those "pushers" from 15 years ago, although I never wrote an app with ADO and Access. – BobRodes Nov 07 '17 at 21:53
  • Thanks for the name of that library. Unfortunately I still have an issue, which I guess I've had all along. Originally when I was trying to load the Microsoft DAO 2.6 Object Library, I would get Error in loading DLL. Since you pointed me to the Office xx.0 Access library, I think I better understand my issue. I have Office 15 for the Office applications and there is also Office 16 for Skype for Business. Only the Office 16 Access is listed in the Available References. When I use the Browse... to select ACEDAO.DLL, nothing happens. Is this the correct file? Am I doing something wrong? – ToniP Nov 09 '17 at 00:09
  • Don't browse for it. It is listed, if it is installed. And remove DAO 2.6. – Gustav Nov 09 '17 at 10:59
  • Only Microsoft Office 16.0 Access Database Engine Object Library is listed. When I select it, I get "Error in loading DLL". – ToniP Nov 13 '17 at 02:00
  • Then either the installation is hosed, or something else is going on. – Gustav Nov 13 '17 at 07:37
0

I see no mention of the standard Query Design View feature. Where are you in terms of using this?

It is quite helpful as once one makes a query using the visual design view, there is an option to put it into SQL View which will give you the syntax.... then one can more easily port that syntax over into a vba statement if one must - although as of the moment it is not clear as to why one must define the query via vba.

Cahaba Data
  • 624
  • 1
  • 4
  • 4
  • That is essentially what I ended up doing and found the correct syntax to be DoCmd.RunSQL "CREATE TABLE [Settings] (ID NUMBER PRIMARY KEY, NumFiles NUMBER, FileDate DATE, FFolder TEXT, FTagname TEXT, FFloat TEXT, FString TEXT, TtlTags NUMBER, TtlFloat NUMBER, TtlString NUMBER, noFloat TEXT, noString TEXT);" Thanks. – ToniP Nov 09 '17 at 00:17