VBA SQL Strings - Tutorial for Beginner
ฝัง
- เผยแพร่เมื่อ 2 มิ.ย. 2024
- In depth tutorial on how to build (SQL) Strings inside the VBA-Code-Editor. How to embed the different data types (numeric, date, decimal, and string) inside SQL strings and how to format longer SQL queries more readable.
A text version of this topic with some extended information is available at codekabinett.com/rdumps.php?La...
The database I used during the video is available for download at this URL: codekabinett.com/download/Str... - วิทยาศาสตร์และเทคโนโลยี
![[Full Episode] MasterChef Junior Thailand มาสเตอร์เชฟ จูเนียร์ ประเทศไทย Season 3 Episode 1](http://i.ytimg.com/vi/Qs5zdBqLn_8/mqdefault.jpg)
God bless you dear sir, lots of respect from India 🙏🇮🇳
Thank you for the interesting Video. It helped me how to put a String variable inside a query.
After days of watching other tutorials this gave me my eureka moment and solved my multiple apostrophe problems.
Danke Sehr ,ein tolles Tutorial
Thank you so much. I have seen a lot of really excellent MS Access programming videos. However, this particular video is the most useful ever! I have encountered more confusion from strings and quotation marks than anything else in this programming language. Thank you for making this clear.
Great help in understanding how to put different values in SQL query in VBA which i was looking for from long time and this is completely cleared my confusion. Thx a ton.
I've been looking all over for a video like this. Excellent video.
Cleared up a lot of confusion on this topic, thanks.
Thank you this helped so much. All I wanted was the Syntax to pass a vba variable to a sql command and I finally found it.
Awesome! It resolved the issues that I was struggling with.
Thank you very much sir!
This is extremely helpful for my interview next week!
Superb video. Thank you very much. Wish there were more teachers like you. !!!
You do a great job in this tutorial. Thanks so much!
I like and say that u r the best programer
Thanks
Excellent narration on basics . Thank you very much....
Really good explanations and code development. Thank you.
excellent video. Thanks
Great tutorial and good explanation...
Love this video, thanks you very much. Now I understand why my slq structure in vba didn't run haha. Kind regards
Excellent ..!Its extremely helpful
Excellent Teaching, very helpful. Thank You.
Very useful , simple
Thank you
Du hast mir den Arsch gerettet mit dem Video, saubere Arbeit. Ich bin fast durchgedreht und alt geworden, aber jetzt geht mein Programm und das alles wegen einem "'"
Love❤️ you my friend, it sorted my critical issues. Thank you so much genius...
Excellent video, thank you.
Thank you so much!
Awesome video....again.
Great tutorial
Wonderful!
Thanks for the video. What is your solution if you have a very long query? Currently I just have my long query as an Access query and use DoCmd.OpenQuery but I would much rather have less Access queries and have the SQL in VBA
Although I'm not entirely sure about the core issue, I love your question.
There's no quick and simple answer. I'm thinking about doing a video on that topic...
Thank You !!
I was trying to make a vba in excel using sql commands to export data to access, your method worked, but for some cases where i had field name ending with a question mark for example, "explicit?" while debugging excel could read the variable that contains "explicit?" but did not print it in access. ... so in other words it worked for all normal text but not for text ending with a question mark, although access accepts a question mark at the end of a field name so what can I do here? thank you
For j = 2 To RowTable
MyString = Worksheets("Sheet1").Cells(j, 1)
MyFeild = Worksheets("Sheet1").Cells(j, 2)
MyFeildType = Worksheets("Sheet1").Cells(j, 3)
If MyFeildType = "COUNTER" Or MyFeildType = "Text(128) " Or MyFeildType = "INTEGER" Then
Else: Worksheets("Sheet1").Cells(j, 3) = "Text(128)"
End If
On Error Resume Next
MyDatabase.Execute "CREATE Table " & MyString & " " _
& "(" & MyFeild & " " & MyFeildType & " CONSTRAINT MyFieldConstraint " _
& "PRIMARY KEY);"
MyDatabase.Execute "ALTER TABLE " & MyString & " " _
& "ADD COLUMN " & MyFeild & " " & MyFeildType & ";"
Next j
Ahmad, thank you for bringing this up. I should have mentioned and shown this in the video.
If your column or table names include special characters or spaces, you should enclose the name in square brackets. E.g.: [explicit?]
Here is your last statement adapted for this scenario:
MyDatabase.Execute "ALTER TABLE [" & MyString & "] " _
& "ADD COLUMN [" & MyFeild & "] " & MyFeildType & ";"
Nevertheless, I recommend NOT to use special characters in table or column names.
It worked! thank you a lot for this video and for your comment :)
Hi There,
I like your lecture a lot. But I have a question to you regarding ShowDataSheet function. Do you write this or it is a defined function in VBA? I use the the same data table and same query as well. But it says ShowDataSheet function is not defined. Can you please clarify us in this regard. It would be appreciated. Thanks in Advance.
Dear Sir, Thanks a lot. I get of it!
Thank you very much for your feedback! The ShowDataSheet function is a custom function. Watch my latest video, there it is explained in detail. -> th-cam.com/video/S_z-Rg4v3wg/w-d-xo.html
Dear Sir, I have a question regarding Runtime Error 3141:
Sometimes my program does not work, it shows Runtime Error 3141. As for example,
Dim sqlString As String
sqlString = "SELECT target_table420.TraderID, target_table420.Side, Sum([target_table420].[EQuantity]*[target_table420].[EPrice]) AS AskAmount," & _
"FROM target_table420" & _
"GROUP BY target_table420.TraderID, target_table420.Side" & _
"HAVING (target_table420.Side=""Ask"");"
DoCmd.RunSQL (sqlString)
Two issues are apparent with your code. 1.) there is a surplus comma before FROM, 2.) RunSQL is meant for DML (Action Queries), it will not work with Select-Queries.
Can someone simply let me know which library is this ShowDataSheet Method located? I cannot call this function even with DAO, Access library activated.
The ShowDataSheet method is a custom method I wrote. Look at the info text of this video. There's a download link to the sample file containing that method.
Thanks that helps!
U r genius
Well done vid. I'd give it 99.5 out of 100. Would have been perfect if the problem of words with apostrophes was covered (e.g. O'Brien).
Just add an extra apostrophe/quote , so that it looks like O’’Brien. (That is 2 single quotes)
SQL will then adjust it. This is certainly the case for SQL Server. Access might not even need it
Klasse - TOP