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.
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...
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 "'"
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.
Thank you very much for your feedback! The ShowDataSheet function is a custom function. Watch my latest video, there it is explained in detail. -> ua-cam.com/video/S_z-Rg4v3wg/v-deo.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.
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
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);"
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.
God bless you dear sir, lots of respect from India 🙏🇮🇳
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.
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 this helped so much. All I wanted was the Syntax to pass a vba variable to a sql command and I finally found it.
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.
Superb video. Thank you very much. Wish there were more teachers like you. !!!
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 for the interesting Video. It helped me how to put a String variable inside a query.
Excellent narration on basics . Thank you very much....
Thank you very much sir!
This is extremely helpful for my interview next week!
Really good explanations and code development. Thank you.
Love this video, thanks you very much. Now I understand why my slq structure in vba didn't run haha. Kind regards
Excellent Teaching, very helpful. Thank You.
Awesome! It resolved the issues that I was struggling with.
Love❤️ you my friend, it sorted my critical issues. Thank you so much genius...
I like and say that u r the best programer
Thanks
You do a great job in this tutorial. Thanks so much!
Excellent video, thank you.
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...
Excellent ..!Its extremely helpful
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 "'"
excellent video. Thanks
Great tutorial and good explanation...
Very useful , simple
Thank you
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. -> ua-cam.com/video/S_z-Rg4v3wg/v-deo.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!
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
Thank you so much!
Wonderful!
Great tutorial
Awesome video....again.
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 :)
Klasse - TOP
U r genius
Thank You !!