UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Create Make Query From Vba, Access 2016    
 
   
buster1020
post Jun 20 2019, 03:55 PM
Post#1



Posts: 9
Joined: 22-June 03



I need to be able to generate a Make Query from within VBA. I am not seeing anything anywhere on how to do it, if it is even possible. The reasons below are why I have to do it this way:

1. User can select any table
2. User can select any or all fields
3. User can name the fields anything they want
4. User can enter selection criteria as they want

So, this means that I have no way of knowing what tables and/or fields they are being selected, as well as any criteria or names they have assigned. How the tool works is:

1. User selects database out of 8 possible (all data tables are linked to MYSQL)
2. User selects table from selected database
3. User will select desired fields, criteria, sort order
4. User clicks Preview Report to generate the report

The report is generated, formatted and displayed. I can make it work but only if no criteria, alias names or sort are used. I am copying the data to a temp table to read the report from. Below is the VBA I am using to generate the report (minus the multiple steps to get to this point, this is not all of the VBA, just the prime VBA).

Thank you in advance for ANY help.

Dim FIELD_CAPTION As String, FIELD_LABEL As String, FIELD_SOURCE As String, FIELD_LABEL_NAME As String, FIELD_SOURCE_LABEL As String
Dim FIELD_BACK_COLOR As String, BACK_COLOR As String, SOURCE As String
Dim dbsCurrentDB As DAO.DATABASE
Dim rstCRITERIA As DAO.Recordset
Dim FIELD_NAME As String, FIELD_ALIAS As String
Dim X As Integer, RECORD_COUNT As Integer, NON_VISIBLE As Integer, Y As Integer, STANDARD_SIZE As Integer, BUFFER As Integer, MAX_CHAR_LEN As Integer

DoCmd.SetWarnings False

STANDARD_SIZE = 11520 '8 * 1440 twips
BUFFER = 0.8 'Amount added as buffer for field length
Me.FRM_AD_HOC_REPORTS_CHAR_LEN_SUB.Requery

MAX_CHAR_LEN = Me.CHAR_LEN

DoCmd.CopyObject , Me.REPORT_NAME, acReport, "RPT_DEV_OPERATIONS_TEMPLATE"
DoCmd.OpenReport Me.REPORT_NAME, A_DESIGN
Set dbsCurrentDB = CurrentDb
Set rstCRITERIA = dbsCurrentDB.OpenRecordset("TBL_TEMP_REPORT_FIELDS")
RECORD_COUNT = rstCRITERIA.RecordCount

'Update report fields with labels and field names
With Reports(Me.REPORT_NAME)
rstCRITERIA.MoveFirst
For X = 1 To RECORD_COUNT
FIELD_NAME = rstCRITERIA!FIELD_NAME
FIELD_ALIAS = rstCRITERIA!FIELD_ALIAS
FIELD_LABEL = "FIELDLABEL" & X
FIELD_SOURCE = "SOURCE" & X
'Update field labels
FIELD_LABEL = "FIELDLABEL" & X
FIELD_CAPTION = FIELD_ALIAS
.Controls(FIELD_LABEL).Width = MAX_CHAR_LEN
.Controls(FIELD_LABEL).Visible = True
.Controls(FIELD_LABEL).Caption = Replace(FIELD_CAPTION, "_", " ")
.Controls(FIELD_LABEL).NAME = FIELD_CAPTION
'Update field source
SOURCE = "SOURCE" & X
.Controls(FIELD_SOURCE).LEFT = MAX_CHAR_LEN
.Controls(FIELD_SOURCE).Width = STANDARD_SIZE - MAX_CHAR_LEN
FIELD_SOURCE = "SOURCE" & X
FIELD_SOURCE = FIELD_NAME
FIELD_SOURCE_LABEL = "FIELD_SOURCE_LABEL" & "lbl"
.Controls(SOURCE).ControlSource = FIELD_NAME
If rstCRITERIA!DATA_TYPE = "TINYINT" Then
.Controls(SOURCE).Format = "YES/NO"
Else
If rstCRITERIA!DATA_TYPE = "DATE" Then
.Controls(SOURCE).Format = "yyyy-mm-dd"
Else
If rstCRITERIA!DATA_TYPE = "DATETIME" Then
.Controls(SOURCE).Format = "yyyy-mm-dd"
Else
If rstCRITERIA!DATA_TYPE = "TIMESTAMP" Then
.Controls(SOURCE).Format = "yyyy-mm-dd"
Else
End If
End If
End If
End If
.Controls(SOURCE).Visible = True
FIELD_CAPTION = "CAPTION" & X
rstCRITERIA.MoveNext

Next X
End With
'Hide labels and fields not being used
With Reports(Me.REPORT_NAME)

Y = 40
For NON_VISIBLE = (RECORD_COUNT + 1) To Y
FIELD_LABEL = "FIELDLABEL" & NON_VISIBLE
.Controls(FIELD_LABEL).Visible = False
SOURCE = "SOURCE" & NON_VISIBLE
.Controls(SOURCE).Visible = False
Next NON_VISIBLE
End With
Go to the top of the page
 
theDBguy
post Jun 20 2019, 04:05 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,723
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

Just curious... Have you checked if there's a demo for this in the Code Archive? If not, it should be possible.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
buster1020
post Jun 20 2019, 06:02 PM
Post#3



Posts: 9
Joined: 22-June 03



Hi theDBguy,

I have searched a number of different ways, multiple forums. Have had no luck at finding anything.
Go to the top of the page
 
WildBird
post Jun 20 2019, 06:58 PM
Post#4


UtterAccess VIP
Posts: 3,578
Joined: 19-August 03
From: Auckland, Little Australia


Where is the new table you are creating,and for what purpose? Is it simply that you want to use the data externally, and could export to Excel instead?

Make table queries will cause bloat and generally can get messy with a lot of tables lying around. Instead of how to do something, maybe tell us why.

Cheers

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
MadPiet
post Jun 20 2019, 09:35 PM
Post#5



Posts: 3,171
Joined: 27-February 09



Let me get this straight. You want to create a Report Wizard type thing that creates queries for you? is there something you don't like about using the available wizards? I guess you could do this, but that looks like a LOT of work.
This post has been edited by MadPiet: Jun 20 2019, 09:56 PM
Go to the top of the page
 
gemmathehusky
post Jun 21 2019, 06:38 AM
Post#6


UtterAccess VIP
Posts: 4,725
Joined: 5-June 07
From: UK


Do you mind users creating new tables?

Why not let them, if you don't mind - of course they will lose anything they do if they reload a new version of the database. Are they just trying to export data to excel, eg?

just create a make table query, and view the SQL for it - then you should see what the SQL needs to look like.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
ADezii
post Jun 21 2019, 07:12 AM
Post#7



Posts: 2,461
Joined: 4-February 07
From: USA, Florida, Delray Beach


I agree with MadPiet, it's a LOT of work.
Go to the top of the page
 
cheekybuddha
post Jun 21 2019, 09:51 AM
Post#8


UtterAccess VIP
Posts: 11,421
Joined: 6-December 03
From: Telegraph Hill


What is the benefit of copying the data and using a MakeTable query?

Why not just use a query?

--------------------


Regards,

David Marten
Go to the top of the page
 
buster1020
post Jun 24 2019, 11:03 AM
Post#9



Posts: 9
Joined: 22-June 03



All,

Let me address your responses:

1. The reason for the MakeTable query is to have the data in the user desired format. This being sort order, field names, any search criteria. The field names have to be used to pull the data. The data is stored temporarily locally as multiple people may be wanting to use the same tables at the same time. I will not know in advance what any of the selected fields, etc are.

2. I know how to create a MakeTable in the query itself, but can't find a way to do that in VBA. I can run select, update, delete SQL statements from VBA but not able to do the MakeTable.

3. The bloat will not be an issue as the user will download a new version every time they open the database.

4. For the Report Wizard question, you can't do what the company wants done via the Report Wizard. That is great for a starting point but not good for end users.
Go to the top of the page
 
theDBguy
post Jun 24 2019, 02:19 PM
Post#10


Access Wiki and Forums Moderator
Posts: 75,723
Joined: 19-June 07
From: SunnySandyEggo


Hi,

QUOTE
Hi theDBguy,

I have searched a number of different ways, multiple forums. Have had no luck at finding anything.


Have you seen this one from the Code Archive?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ADezii
post Jun 24 2019, 02:40 PM
Post#11



Posts: 2,461
Joined: 4-February 07
From: USA, Florida, Delray Beach


I am actually assisting a User in another Thread with a 'similar type' request. The User needs to dynamically create a Table(s) as well as any number of associated Fields for that Table. All the info necessary for this to happen is contained in a Table named tblIndex (Table Name, Field Names, Field Type, Data Type Values, Field Size, and Format Strings). The User then selects the Table from a Combo Box and all of the Fields associated with that Table are displayed in a Multi-Select Listbox. The User then selects 1 or more Fields from the List Box and a Temporary Table is created (TEMP_ & Table Name). All of the information required in creating these Tables are contained in tblIndex and are retrieved from Hidden Columns in the List Box. I'm not really sure if you will find this useful, but I'll Upload it now and you decide if you can utilize anything from it. Keep in mind that this is a work in progress and is in its initial developmental stage. Good Luck with your Project.
Attached File(s)
Attached File  Dynamic.zip ( 31.65K )Number of downloads: 12
 
Go to the top of the page
 
cheekybuddha
post Jun 24 2019, 03:14 PM
Post#12


UtterAccess VIP
Posts: 11,421
Joined: 6-December 03
From: Telegraph Hill


I still can't see the point of creating a new table. shrug.gif

Why not just create a query based in the user selected fields/criteria and load that as the RecordSource of the report?

--------------------


Regards,

David Marten
Go to the top of the page
 
WildBird
post Jun 24 2019, 03:45 PM
Post#13


UtterAccess VIP
Posts: 3,578
Joined: 19-August 03
From: Auckland, Little Australia


If you want to present data to the users, in a sort order or anything, I would go the export to an Excel Pivot table. Much more useful I find than an Access table for end users to do any analysis on.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
buster1020
post Jun 26 2019, 11:47 AM
Post#14



Posts: 9
Joined: 22-June 03



ADezii,

Thank you for the file. Will take a look, sounds like it is similar to what I am trying to do.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th July 2019 - 06:35 PM