Thank you Andrew as always, this was a very interesting tutorial, brilliant 👏👏👏 I love the insert and delete option, my mind is already going in how I can use this in my small report, I hope you will show us more🤔
Thanks Frik! Wednesday's video will show how to select data into a new worksheet or workbook. Friday's video (if I have time to make it) will show how to edit existing data.
Hello andrew, i hope you been so well. i would like to ask how can i handle when i try to do a dynamic Insert into strSQL = "INSERT INTO [" & tableName & "$] (" & Join(columnNames, ", ") & ") VALUES " & StrValues . the system always assumen the data i try to stored the data inside the sheet always put the numeric data like strings
Hi Andrew. Fantastic tutorial as always. Apologies if this has been answered elsewhere in the ADO/Sql playlists. Some of the cells in my source worksheet (that I'm selecting from) contain more than 255 characters. It causes an error. I have researched many posts on the web for a solution, but haven't found anything that works. For now I'm splitting the source data to multiple rows, but I'm hoping there is a more elegant solution.
Hi Andrew, unlike your example of coping data from another closed workbook, is it possible to insert a range of cells on your current workbook into a closed workbook? The use case is when team members each have their own workbook book to record the data and once they done a few entries they can insert their data into the team’s consolidated worksheet. I’d like to use ADO to increase the performance. Thanks.
Hi Andrew, thank you for the video. Recently, I started playing with MySQL. I used your movies database to create a new database in MySQL. I hope you don't mind.
@@WiseOwlTutorials I had to download all the tables as CSV files and import them into MySQL. Movies database is the first database that I first started SQL programming. Thank you very much for such amazing videos as always.
Hi there! I have tried that code however I am getting error , I have sent an email to You as well as I have attached screen shot in that an email, please check it screen shot.
Hi there! I'm sorry but we don't offer a help service by email. If we did that for everyone who asked we wouldn't have any time to do any paid work! You can post your code here and I may get chance to look but you may have more success posting a question at StackOverflow.com - there are thousands of people waiting to answer questions there so you're more likely to get a quick answer! I hope it helps!
this is query: SQLCommand = _ "insert into [Film$] ([Tilte],[Release Date],[Studio],[Boxoffice])" & _ "Select New.[Title],New.[Release Date],New.[Distributor],New.[Worldwide gross] from (" & _ " Select [Title],[Release Date],[Distributor],[Worldwide gross] From [Films2017]" & _ " union all Select [Title],[Release Date],[Distributor],[Worldwide gross] From [Films2018]" & _ ") as New " I was getting error info : Error Number : -2147467259 Error Description: Operation must use an updateable Query Could You please me sir?
@@munichandrakuppani8103 Hi! You have some mistakes in your column names - it's [Title] not [Tilte] and [Box office] not [Boxoffice]. The error message that you're seeing isn't related to those spelling mistakes, however. Here is some information on why you might see that message docs.microsoft.com/en-us/office/troubleshoot/excel/fails-accessing-excel-through-odbc If you haven't already done so, download the example files from the link in the video description. I've copied your code into this file, corrected the spelling mistakes and it works correctly.
Hi Andrew, Thank you so much for this tutorial, I have a question I don't know if that is possible or not, however I wanted to try my luck that's why I am asking this question. Is there any way we can insert a row at the very top (at row number 2, after heading row) in an existing workbook through excel vba sql insert command? Why I want to do this? I am in MS-Access environment and I need to import a very large (approx 50-100 mb file size, and around 600000 row data) excel file in a MS-Access table through VBA code, means in MS-Access in a form there will a button and when the user will click on that button then the user will be able to select an excel file to important and that file will be imported in a MS-Access table through below code line. DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "MS-Access Table Name", ExcelFilePath, True However all the data is not importing in the MS-Access table because of the datatype conversion error, to overcome this problem what I did is that I changed the datatype to "ShortText" for all the columns in MS-Access table, and again when I run above "DoCmd" command to import the data it still shows the error and not importing all the data and showing same datatype conversion error, so I did some research and found that on the time of importing the Excel data internally MS-Access converts the datatype based on the value in the 1st row for each column, then it imports the data, and for that reason I want to insert a garbage text at the very top row to treat all the columns as a text. When manually I inserted a garbage text like "zzzzzzzzzzzz" in the first row for all the columns and then saved the excel file and then in the MS-Access when I run the "DoCmd" command to import the Excel file, it imported all the data successfully. This is the only reason I am seeking a way to insert a text value at the very top row in the excel file then will use the "DoCmd" command to import the data. I can use Workbook.Open command to open the excel file and then insert a row at the top of the sheet and put some garbage text and then save/close the file and then use "DoCmd" command to import the excel data, however I don't want to apply this approach as I already mentioned that excel file is very very large and also that file saved at a Network location and when I am opening the file through "Workbook.Open" command it is taking a lot of time to open the file and sometimes excel hanged/crashes, that's why I need to insert a row/data in the top row without opening the excel file through excel insert sql command, is there any way to achieve this through vba sql insert command? Thanks in advance for reading this comment. Please do take care of yourself and your family and stay safe and healthy. Thanks Kashif
Hi Kashif! The short answer is no, you can't control the position of the inserted row. Take a look at the next video in this playlist ua-cam.com/video/z5zYtVNcNnQ/v-deo.html which shows you how to insert data into new worksheets and workbooks. You could use a UNION query to insert a dummy row into a new copy of your data file with an ORDER BY clause to control the position of the dummy row. Here's a basic example of the code SQLCommand = _ "SELECT [Film ID], [Title], [Release Date], [Run Time] " & _ "INTO [Results] IN '" & ThisWorkbook.Path & "\Copy of Data.xlsx' 'Excel 12.0 Xml;' " & _ "FROM " & _ "(SELECT [Film ID], [Title], [Release Date], [Run Time] FROM [Film$] " & _ "UNION SELECT -1, 'Dummy Text', 'Dummy text', 'Dummy text' FROM [Film$]) AS t " & _ "ORDER BY [Film ID] ASC" You might like to check out the videos on using UNION queries in the same playlist if you're not sure how that part works. I hope it helps!
Sir video is fentastic and informative. Thanks much.
You're very welcome Rohith, glad you enjoyed it!
Hats off brilliant
Thanks Dave!
Thank you Andrew as always, this was a very interesting tutorial, brilliant 👏👏👏 I love the insert and delete option, my mind is already going in how I can use this in my small report, I hope you will show us more🤔
Thanks Frik! Wednesday's video will show how to select data into a new worksheet or workbook. Friday's video (if I have time to make it) will show how to edit existing data.
@@WiseOwlTutorials fantastic Andrew
Thanks you very much
You're very welcome, thank you for watching!
Marvelous!
Thanks Jonathan!
Hello andrew, i hope you been so well. i would like to ask how can i handle when i try to do a dynamic Insert into
strSQL = "INSERT INTO [" & tableName & "$] (" & Join(columnNames, ", ") & ") VALUES " & StrValues . the system always assumen the data i try to stored the data inside the sheet always put the numeric data like strings
Hi Andrew. Fantastic tutorial as always. Apologies if this has been answered elsewhere in the ADO/Sql playlists. Some of the cells in my source worksheet (that I'm selecting from) contain more than 255 characters. It causes an error. I have researched many posts on the web for a solution, but haven't found anything that works. For now I'm splitting the source data to multiple rows, but I'm hoping there is a more elegant solution.
Thanks Deon! Sadly I don't know of a solution to that problem either, I'm sorry!
Hi Andrew, unlike your example of coping data from another closed workbook, is it possible to insert a range of cells on your current workbook into a closed workbook? The use case is when team members each have their own workbook book to record the data and once they done a few entries they can insert their data into the team’s consolidated worksheet. I’d like to use ADO to increase the performance. Thanks.
Hi! Try starting at video 58.28 in this playlist ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html
I hope it helps!
@@WiseOwlTutorials Thank you so much!
Hi Andrew, I have a query here. Will this technique work if the target workbook is on shared drive and someone already kept it open?
Honestly I'm not sure! I think you'll have to test it.
Hi Andrew, thank you for the video. Recently, I started playing with MySQL. I used your movies database to create a new database in MySQL. I hope you don't mind.
That's fine Ravi, I hope you have fun with MySQL!
@@WiseOwlTutorials I had to download all the tables as CSV files and import them into MySQL. Movies database is the first database that I first started SQL programming. Thank you very much for such amazing videos as always.
@@tejamarneni My pleasure Ravi, thank you for your support as always!
🥰🥰🥰🥰🥰
Hi there!
I have tried that code however I am getting error , I have sent an email to You as well as I have attached screen shot in that an email, please check it screen shot.
Hi there! I'm sorry but we don't offer a help service by email. If we did that for everyone who asked we wouldn't have any time to do any paid work! You can post your code here and I may get chance to look but you may have more success posting a question at StackOverflow.com - there are thousands of people waiting to answer questions there so you're more likely to get a quick answer!
I hope it helps!
this is query:
SQLCommand = _
"insert into [Film$] ([Tilte],[Release Date],[Studio],[Boxoffice])" & _
"Select New.[Title],New.[Release Date],New.[Distributor],New.[Worldwide gross] from (" & _
" Select [Title],[Release Date],[Distributor],[Worldwide gross] From [Films2017]" & _
" union all Select [Title],[Release Date],[Distributor],[Worldwide gross] From [Films2018]" & _
") as New "
I was getting error info :
Error Number : -2147467259
Error Description: Operation must use an updateable Query
Could You please me sir?
@@munichandrakuppani8103 Hi! You have some mistakes in your column names - it's [Title] not [Tilte] and [Box office] not [Boxoffice].
The error message that you're seeing isn't related to those spelling mistakes, however. Here is some information on why you might see that message docs.microsoft.com/en-us/office/troubleshoot/excel/fails-accessing-excel-through-odbc
If you haven't already done so, download the example files from the link in the video description. I've copied your code into this file, corrected the spelling mistakes and it works correctly.
@@WiseOwlTutorials Thank You so much
@@munichandrakuppani8103 You're very welcome!
Hi Andrew,
Thank you so much for this tutorial, I have a question I don't know if that is possible or not, however I wanted to try my luck that's why I am asking this question.
Is there any way we can insert a row at the very top (at row number 2, after heading row) in an existing workbook through excel vba sql insert command?
Why I want to do this?
I am in MS-Access environment and I need to import a very large (approx 50-100 mb file size, and around 600000 row data) excel file in a MS-Access table through VBA code, means in MS-Access in a form there will a button and when the user will click on that button then the user will be able to select an excel file to important and that file will be imported in a MS-Access table through below code line.
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "MS-Access Table Name", ExcelFilePath, True
However all the data is not importing in the MS-Access table because of the datatype conversion error, to overcome this problem what I did is that I changed the datatype to "ShortText" for all the columns in MS-Access table, and again when I run above "DoCmd" command to import the data it still shows the error and not importing all the data and showing same datatype conversion error, so I did some research and found that on the time of importing the Excel data internally MS-Access converts the datatype based on the value in the 1st row for each column, then it imports the data, and for that reason I want to insert a garbage text at the very top row to treat all the columns as a text.
When manually I inserted a garbage text like "zzzzzzzzzzzz" in the first row for all the columns and then saved the excel file and then in the MS-Access when I run the "DoCmd" command to import the Excel file, it imported all the data successfully.
This is the only reason I am seeking a way to insert a text value at the very top row in the excel file then will use the "DoCmd" command to import the data.
I can use Workbook.Open command to open the excel file and then insert a row at the top of the sheet and put some garbage text and then save/close the file and then use "DoCmd" command to import the excel data, however I don't want to apply this approach as I already mentioned that excel file is very very large and also that file saved at a Network location and when I am opening the file through "Workbook.Open" command it is taking a lot of time to open the file and sometimes excel hanged/crashes, that's why I need to insert a row/data in the top row without opening the excel file through excel insert sql command, is there any way to achieve this through vba sql insert command?
Thanks in advance for reading this comment.
Please do take care of yourself and your family and stay safe and healthy.
Thanks
Kashif
Hi Kashif!
The short answer is no, you can't control the position of the inserted row.
Take a look at the next video in this playlist ua-cam.com/video/z5zYtVNcNnQ/v-deo.html which shows you how to insert data into new worksheets and workbooks. You could use a UNION query to insert a dummy row into a new copy of your data file with an ORDER BY clause to control the position of the dummy row. Here's a basic example of the code
SQLCommand = _
"SELECT [Film ID], [Title], [Release Date], [Run Time] " & _
"INTO [Results] IN '" & ThisWorkbook.Path & "\Copy of Data.xlsx' 'Excel 12.0 Xml;' " & _
"FROM " & _
"(SELECT [Film ID], [Title], [Release Date], [Run Time] FROM [Film$] " & _
"UNION SELECT -1, 'Dummy Text', 'Dummy text', 'Dummy text' FROM [Film$]) AS t " & _
"ORDER BY [Film ID] ASC"
You might like to check out the videos on using UNION queries in the same playlist if you're not sure how that part works. I hope it helps!
Thanks you very much
You're very welcome, thank you for watching!