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
> Ms Access SQL View To Visual Basic, Access 2010    
 
   
pbgradyD
post May 16 2018, 08:49 AM
Post#1



Posts: 38
Joined: 11-March 16



Hello
I can create queries in access and I notice the SQL View tab that displays what the design view is actually doing.
My question is can I use the SQL view in VB? I know I cannot just copy the SQL view into a VB module, or I dont think so, but the SQL View should be how my query in VB looks for the most part?
If not Im sure their is a good tutorial on here on how to create queries in VB?
thanks
Go to the top of the page
 
zaxbat
post May 16 2018, 08:54 AM
Post#2



Posts: 1,180
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


With some minor exceptions, yes, the SQL view verbage can be assigned to a string variable and then passed to the SQL engine in several ways.

I usually do it like this.
CODE
private sub runsomesqlinVBA()
dim mySQLtxt as string

   mySQLtxt = "UPDATE Employees SET Country = 'United States' WHERE Country = 'USA'"

   docmd.setwarnings false    'do this so the user will not be bothered with SQL messages
   docmd.runSQL mySQLtxt
   docmd.setwarnings true     'turn warning messages back on now, might be something important in them


'Many peeps use this method
dim mydb as dao.database

   set mydb = currentdb
   mydb.execute mySQLtxt, dbFailOnError

'you can even create a query in VBA and use it temporarily or even have it saved in your navigator list for use just like any of your hand entered queries

end sub


The biggest difference between SQL view code and VBA SQL code seems to involve the use of double quotes vs. single quotes. And that can really turn into a sticky issue. To my knowledge you often are prevented from using double quotes in the query designer.....but in the VBA generated queries they are perfectly acceptable as long as you can keep them straight.
This post has been edited by zaxbat: May 16 2018, 09:15 AM

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
Phil_cattivocara...
post May 16 2018, 09:15 AM
Post#3



Posts: 199
Joined: 2-April 18



QUOTE (zaxbat)
CODE
mySQLtxt = " SELECT * FROM mytable;"
(deleted)
Edit: you changed your post. Nothing to say.
This post has been edited by Phil_cattivocarattere: May 16 2018, 09:18 AM

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
zaxbat
post May 16 2018, 09:18 AM
Post#4



Posts: 1,180
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Right, thanks for that, Phil. I often forget since most of my queries are action queries.

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
pbgradyD
post May 16 2018, 12:29 PM
Post#5



Posts: 38
Joined: 11-March 16



OK, thanks, let me see if I can get it to work
You mentioned that If I create the query in a VB module that it would still show up in my list of queries?
Do I have to add something in the syntax to make that possible?

Thanks let me see if I can get the query to work
Go to the top of the page
 
pere_de_chipstic...
post May 16 2018, 01:33 PM
Post#6


UtterAccess Editor
Posts: 10,261
Joined: 8-November 07
From: South coast, England


PMFJI

When using CurrentDb.Execute, you also have to be careful that any parameters are resolved correctly
e.g.
CODE
Dim strSQL As String

strSQL = "UPDATE tblMembers SET tblMembers.Notes = '123' WHERE (((tblMembers.MemberID)=[Forms]![frmMembers]![lstMembers]));"
CurrentDb.Execute strSQL, dbFailOnError
will fail

you would need to use:
CODE
Dim strSQL As String

strSQL = "UPDATE tblMembers SET tblMembers.Notes = '123' WHERE (((tblMembers.MemberID)=" & [Forms]![frmMembers]![lstMembers] & "));"
CurrentDb.Execute strSQL, dbFailOnError

hth

--------------------
Warm regards
Bernie
Go to the top of the page
 
zaxbat
post May 16 2018, 06:15 PM
Post#7



Posts: 1,180
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


To make the query save to your navigator list on the left you do have to create a querydef in your db. It is a bit involved and tricky and I do not think that you need it. But I'm sure somebody will get the syntax for you.

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
cheekybuddha
post May 17 2018, 05:44 AM
Post#8


UtterAccess VIP
Posts: 10,257
Joined: 6-December 03
From: Telegraph Hill


QUOTE
To my knowledge you often are prevented from using double quotes in the query designer

confused.gif

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


Regards,

David Marten
Go to the top of the page
 
pbgradyD
post May 17 2018, 02:36 PM
Post#9



Posts: 38
Joined: 11-March 16



Ok
I have been trying to get the query to work all day and researching
I was thinking that I could create make table query. The query itself is pulling data from linked oracle inhouse tables. I create the query based on these and want to output the table.
I am not 100% sure that if someone added data to the oracle in house tables while I was using the table that I created if it would be updated, or would I have to re-run the query

Another issue I have is that I have criteria on 2 of my fields and I also have 2 fields created from the expression builder and Im not sure if just posting the SQL view code is working
Can I upload a sample access database or is that not proper method? Or I can post the SQL view

You all have helped me a lot I am still trying to learn vb and SQL bc I believe or hope the procedures will operate faster and less likely to be tampered with by other users so thank you
Go to the top of the page
 
projecttoday
post May 17 2018, 03:01 PM
Post#10


UtterAccess VIP
Posts: 9,964
Joined: 10-February 04
From: South Charleston, WV


All day? So what is your objective? You say you are using VB. Are you using Visual Basic .Net? Or do you mean VBA?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
zaxbat
post May 17 2018, 03:09 PM
Post#11



Posts: 1,180
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


You can certainly do either or both. Only thing is you should not put live data about real people/businesses in here. Swap out fake names, phone numbers, and addresses often works just fine. You should compress & repair your DB and then zip it or else UA will not allow the upload. If that seems like a lot of trouble....you can just cut and paste the SQL into your message here.
This post has been edited by zaxbat: May 17 2018, 03:10 PM

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
pbgradyD
post May 17 2018, 03:39 PM
Post#12



Posts: 38
Joined: 11-March 16



OK
I did remove all information pertaining to names and important info and added data that would be of the similar table structures
For one of the fields in the oracle table it is set as a memo field
I am using this to convert it to a text field:

WELL_EVENT_DESC: Left([NARRATIVE],255)

I am using the query in another application and this method seems to work, but with just the memo field the text did not show up., i have it set to 255 bc I think this is the max character count

I found the code by googling convert memo to text

I appreciate the help a lot


Attached File(s)
Attached File  Qry_in_VBA_Mod.zip ( 26.17K )Number of downloads: 7
 
Go to the top of the page
 
AlbertKallal
post May 18 2018, 01:52 AM
Post#13


UtterAccess VIP
Posts: 2,654
Joined: 12-April 07
From: Edmonton, Alberta Canada


Actually, what I often do is keep the SQL in that query, and then use the SQL in vb WITHOUT copying the SQL into the VBA editor.

This has some great advantages:

You don’t have “messy” SQL in VBA

You can change the query, and if the query is used “many” places in VBA (such as adding a new column), then you don’t have to go on some huge VBA edit and track down the SQL all over the place – it remains in once place.

You don’t have to write the SQL by hand, but can continue to us ehte query builder – and that includes the graphical view.

So in place of pasting in the SQL into VBA, I often simply do this:

CODE
Dim strSQL as string
Dim rst    as dao.RecordSet

strSQL = currentdb.querydefs("MyQuery").SQL
replace(strSQL,";","")

strSQL = strSQL & " and City = 'Edmonton’"

set rst = currentdb.OpenReocrdSet(strSQL)



So the above allows you keep the SQL as a query, but use the SQL in code, but without all the messy SQL in VBA.

And you can even replace parmaters, and I often do that.

So if the above query in the query builder had a paramters for City, say [MyCity]

Then your code becomes

CODE
Dim strSQL as string
Dim rst    as dao.RecordSet

strSQL = currentdb.querydefs("MyQuery").SQL
replace(strSQL,";","")

replace(strSQL,"[MyCity]","'Edmonton’")

set rst = currentdb.OpenReocrdSet(strSQL)


You can also of course even use the parameters collection of the querydef if you “just” going to replace some parameters in the SQL and not really modify the SQL on the fly (that “on the fly” ability of VBA SQL is a great reason to use “in-line” SQL because “ahead” of time, you don’t have to know or define the parmaters.

So if you not doing “on the fly” changes to SQL, and you “know” a given parmater as above shows?

Then you can use this code, and it even somewhat better.

CODE
   Dim rst     As DAO.Recordset
  
   With CurrentDb.QueryDefs("qryHotelsTest")
      .Parameters("[MyCity]") = "Calgary"
      Set rst = .OpenRecordset()
   End With


Not only is above nice, but you also don’t have to worry about delimiters (quotes) anymore. Say we had city, and todays date as 2 parameters, the above then becomes

CODE
   Dim rst     As DAO.Recordset
  
   With CurrentDb.QueryDefs("qryHotelsTest")
      .Parameters("[MyCity]") = strMyCity
      .Parameters("[InvoiceDate]") = Date()
      Set rst = .OpenRecordset()
   End With


So you can directly use VBA variables. In the above, I could replace “date() (today) with a VBA date variable, and again no formatting of the date, or no “#” etc. need be placed in the code.

So this approach means that you can use SQL from a query directly in your VBA, but you avoid the messy cut + paste, and down the road to modify the query, you can use the query builder – even in the graphical view. This will most certainly reduce coding errors, since you let Access do all the heavy lifting in terms of creating and writing the SQL.

Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
pbgradyD
post May 18 2018, 10:33 AM
Post#14



Posts: 38
Joined: 11-March 16



I am using VBA in MS Access 2010
I can create the query using the query builder but i am trying to learn how to create the query using VBA in a module and have it create a new table every time the access database is opened.
Prior I was trying to create a select query using VBA in a module that would output a query in the query list
But I think if I can get it to just create a table that that should be sufficient; the data is coming from oracle tables that I cant edit or add to but extract what i need and make my own queries
The main reason is Im trying to learn more VBA in access to customize, I do find code on this site that I can usually edit and get it to work for my data, but this one Im having some issues with setting it up
If I google: MS Access 2010 make table query using VBA module I see some examples but my syntax is off or I just dont have enough knowledge
Also, I want to know if this method is faster, the actual query that I create pulls a lot more records then the one that I uploaded. I then use these queries or tables and use them in another software package(ArcGIS) and the data is always up to date

Here is the SQL View from my query:
SELECT DISTINCT tbl_Unique_Well.UNIQUE_ID, tbl_Unique_Well.WELL, tbl_Well_Event_Codes.WELL_EVENT_CODE, tbl_Well_Event_Codes.WELL_EVENT_FLAG, Left([NARRATIVE],255) AS WELL_EVENT_DESC, tbl_Unique_Well_Report.DEPTH, tbl_Unique_Well.KB, [DEPTH]-[KB] AS DEPTH_KB INTO tbl_From_Qry_Test
FROM (tbl_Unique_Well INNER JOIN tbl_Unique_Well_Report ON tbl_Unique_Well.UNIQUE_ID = tbl_Unique_Well_Report.UNIQUE_ID) INNER JOIN tbl_Well_Event_Codes ON tbl_Unique_Well_Report.SN_EVNT = tbl_Well_Event_Codes.SN_EVNT_FK
WHERE (((tbl_Well_Event_Codes.WELL_EVENT_CODE)="DRILL" Or (tbl_Well_Event_Codes.WELL_EVENT_CODE)="LOSS") AND ((tbl_Well_Event_Codes.WELL_EVENT_FLAG)="Y"));

Here is how I am following some of the steps offered:
Private Sub runsomesqlinVBA()
Dim mySQLtxt As String

mySQLtxt = "SELECT DISTINCT tbl_Unique_Well.UNIQUE_ID, tbl_Unique_Well.WELL, tbl_Well_Event_Codes.WELL_EVENT_CODE, tbl_Well_Event_Codes.WELL_EVENT_FLAG,"
'Left([NARRATIVE],255) AS WELL_EVENT_DESC, "
tbl_Unique_Well_Report.DEPTH, tbl_Unique_Well.KB, [DEPTH]-[KB] AS DEPTH_KB INTO tbl_From_Qry_Test"
'"FROM tbl_Unique_Well INNER JOIN tbl_Unique_Well_Report ON tbl_Unique_Well.UNIQUE_ID = tbl_Unique_Well_Report.UNIQUE_ID) INNER JOIN tbl_Well_Event_Codes ON tbl_Unique_Well_Report.SN_EVNT = tbl_Well_Event_Codes.SN_EVNT_FK"
'"WHERE tbl_Well_Event_Codes.WELL_EVENT_CODE)="DRILL" Or (tbl_Well_Event_Codes.WELL_EVENT_CODE)="LOSS") AND ((tbl_Well_Event_Codes.WELL_EVENT_FLAG)="Y"));

End Sub

I do like how one user stated that I do not have to list each field, that I can just name it something and let it run, the query or table data will not have any user adding more fields or changing data, it will always stay the same way that I have set it up, No editing
So yes, I have been researching how to get it to work, I realize what I have above is not enough
Go to the top of the page
 
zaxbat
post May 18 2018, 11:37 AM
Post#15



Posts: 1,180
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Here you go.....look at the VBA to see where your problem was. When the VBA SQL goes beyond one line you need to understand how to continue the code onto following lines. If you are in the middle of a string then you must end it with a ("), then a space then a concatenation character (&), then another space, and then a continuation character (_). If you have a variable at the break between lines then it is not a string at that point and you will not need the (").
If you like, you can actually put the (&) concatenation character on the following line...user preference. I prefer the concatenation and continuation chars to stay at the far right to keep the left end cleaner and easier to analyze. Oh, and you can put in as many spaces as you like. SQL will never balk at extra spaces but it will fall flat if you omit a needed space somewhere. Below are some examples.... One thing you must avoid is comments (unless access has fixed this). To my knowledge, comments after a continuation character gets things all screwed up.

CODE
      mySQLtxt =    " Select " _
                      &   "     *   " & _
                           " FROM " & _
                           " tblMyTable " & _
                           " WHERE tblMyTable.mymainID = " & lngMyMainIDvariable & _
                           " AND tblMyTable.Lastname = '" & txtMyLastNameVariable & "' " & _
                           " ORDER BY mydate;"

Notice that the variable lngMyMainIDvariable is outside the string (outside of the double quotes). Since it is a number, it needs no special treatment. But on the next line notice that txtMyLastNameVariable requires single quotes around it. In truth, this treatment is not sufficient in the event that a last name contains a single quote e.g. O'Mally. That really messes things up and requires special treatment to make it work. But that is not specific to the query being run in VBA, it would be required to handle names like O'Mally even if the query were made and run from the query designer. Dates are also tricky and need to be surronded by the (#) character...but that is getting outside the scope of this demo.

In recap, your VBA query needed only two things to work. 1) you did not handle the line continuation properly, and 2) the double quotes around string comparitors needed to be changed to single quotes.
This post has been edited by zaxbat: May 18 2018, 11:41 AM
Attached File(s)
Attached File  Qry_Created_in_VBA_MOD_Rev.zip ( 35.1K )Number of downloads: 6
 

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
pbgradyD
post May 18 2018, 12:44 PM
Post#16



Posts: 38
Joined: 11-March 16



I see! That helps out tremendously bc now I can practice this on other queries, I wasn't sure about the syntax.
Thank you
Last question, does the code have to be tied to a form? I like the form, to see the results, but I need it to have the refresh or re-run query?

Thank you very much, just that allows me to manipulate some other queries by knowing the proper syntax
Go to the top of the page
 
zaxbat
post May 18 2018, 12:55 PM
Post#17



Posts: 1,180
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


For our purposes, I wanted you to be able to see the results of running the VBA query....so I connected it to a form. But no, if you have some other avenue of triggering the VBA code...it will run in the background and create that table with no need for a form or requery or anything else.

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
pbgradyD
post May 18 2018, 02:27 PM
Post#18



Posts: 38
Joined: 11-March 16



I understand. Thank you for your help with this.
Go to the top of the page
 
zaxbat
post May 18 2018, 03:02 PM
Post#19



Posts: 1,180
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


thumbup.gif

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th August 2018 - 01:02 AM