By far, the best instructor, most comprehensive knowledge on the subject matters. Thank you for sharing your gift & knowledge. Have not seen new material lately. Hope you are well.
You're amazing Andrew!! I have been watching all your videos for the past year and I have grown exponentially in my coding capabilities! Keep up the amazing and work and THANK YOU!
Excellent training videos ... you are well organized and provide good examples and are very articulate. I appreciate all the work you put in towards making these videos.
Simply amazing Andrew! The detail you go through in each and every videos is really praise worthy. Also i really love the enthusiasm with which explain the concepts. :) you truly are a gem! in other videos tutorials for any other subjects, i get easily distracted even if they are for a couple of minutes. but these videos i can watch for hours :)
Wow Sir, this is an informative video which blows my mind and the content which is in this video is something which I even can't imagine especially drop statement in cells that's a fantastic superb thank you so much sir again
Sir waiting for new videos upload on Excel VBA series. Please let us know by when new series would be uploaded. I have learned a lot by watching your video. Thanks a lot.
In the error handler you can clear the terror after the message and resume above the exits sib so that you close the connection in only one place and also exit only once
Andrew, thanks for the proper video, very helpful. I already implemented a query that writes multiple lines into an accdb file. Works brilliantly. I am now trying to modify the code to only insert new data. In sql it would be a code like: insert (x,y) values(x,y) where x not in (select distinct x from db). How do I implement it from excel ADODB? Do I need to use a recordset to select from the db before I can fill it with sql or can I add the Where statement? Thanks for a hint. Best, Michael
So glad to have found your videos. They are excellent and thanks for sharing so much knowledge. I'm following along at home and getting a very odd problem around minute 41-42. My access database isn't letting me execute the first query in the loop: "INSERT INTO tblFilm (FilmID, FilmName, FilmReleaseDate, FilmRunTimeMinutes) VALUES ( (SELECT max(FilmID) FROM tblFilm) + 1, 'Marvel''s The Avengers', '2012-05-04', 143)". If I change the subquery (SELECT max(FilmID) FROM tblFilm) it works just fine. And if I run the main query with a given number (999 for instance), the query also runs just fine. I didn't have any issues with the recordsets in the prior videos. It may be related: but I can't execute the queries into the Access database without a FilmID (that is, the autoincrement isn't working). Any ideas what I'm doing wrong?
Hi WiseOwlTutorials Awesome videos to learn more thanks a lot sharing. i have one question: Are we able to connect the sql which is available in (IP Address) Server. if yes please let me know how we can do it??? Thanks in advance.
Grateful for the video, really helpful! Thank you! Please be so kind and help me figure this out: - lets say, instead of FilmTime as Integer, where numbers are whole, my excel table has decimal numbers. Data type in SQL is set to decimals (30,28, not null), so naturally FilmTime as integer is blocking my decimals.. My questions is, what data type in VBA should I set my FilmTime as? and what format in values? any suggestions?
Hi Ania! You could use the Double VBA data type to successfully pass values to a DECIMAL type in SQL. You might also find this reference table helpful docs.microsoft.com/en-us/office/vba/access/concepts/error-codes/comparison-of-data-types I hope it's useful!
@@WiseOwlTutorials Thank you for a quick answer. Your suggestion worked as I formatted the FileTime value: Replace(Format(FilmTime, "#0.00"), ",", ".") & ");" I have another challenge to solve: my primary key 'ID' does not except duplicates. Is it possible to change if statement: IF instr(1, SQLStr, "drop", vbTextcompare) 0 or add IF NOT EXISTS statement before INSERT statement to check if there are any duplicates: SQLstr= _ "IF NOT EXISTS (SELECT 1 FROM tblFilm WHERE FilmID = '" & FilmID & "') " & _ "insert into tblFilm (" & _
@@aniapirc9485 Hi Ania! Yes, indeed you can: cmd.CommandText = _ "IF NOT EXISTS (SELECT TOP 1 * FROM dbo.Actor WHERE ActorID = 4053) " & _ "INSERT INTO dbo.Actor (FirstName, FamilyName, DoB) " & _ "VALUES ('Clever', 'Cat', '1992-06-24')" I haven't concatenated the id value in this example but you can do so if you need to. I hope it helps!
good morning wiseowl, thanks for the great video it was very helpful in learning using sql statements with activex data objects. I would like to ask how to code the insert query to be able to insert data in a single field coming from two textbox controls. just like concatenating the two controls to store in a single field in a database properly. I would appreciate your response regarding my query about the insert query.
Hi! Can you tell how we can use SQL Stored Procedures with ADO? For example my DB name is DB_Common and my Stored Proc is named spUpdateVol, how would I use it with Command?
Hi Andrew, your content is gold! I have learned more in 2 hours with your tutorials than in months on my own! The seemingly trivial, small things that you do are quite a discoveries to me (-I've been doing this wrong all this time....). I'm definitely donating you a few £ for these brilliant tutorials :) One question though, if I would write a function which would query the database and return a recordset - how to go about closing the connection? I mean, if I close the connection within the function, before returning the recordset I invalidate the recordset, right?
Hi, Awesome videos on ADO... Made my life easier at work... Thanks for all of your Awesome videos... Are you planning on releasing videos on ADOX? And is ADOX better than ADO in excel?
Hi. Thanks for these helpfull tutorials. My question is about the passwords. How to enter password during opening connection with the access database with password protection?
Hey Andrew, you are simply awesome. Appreciate your effort in making these videos and helping us learn new things. I have one question - is there a way to convert Excel VBA macros into an exe file.
Hi Could you please tell me how to display the result of a SELECT query in excel sheet with ADODB.Command method? I'd like something like .CopyFromRecordset method
Hi Andrew, that’s a very helpful video - but unfortunatly I‘m still stuck with a problem: After editing some cells in workbooks(a).worksheets(a) I would like to save the whole UsedRange of worksheets(a) to workbooks(b).Worksheets(a) using ADODB, this way saving a copy of worksheets(a) to workbooks(b) without opening workbooks(b) at all. Both worksheets(a) are identical (columnheaders, UsedRange) in both workbooks. I can copy the worksheet using rs.AddNew and rs.Update one time allright - but from then on I will just add the UsedRange again and again instead of updating the existing data. I‘ve tried the UPDATE command but cant get it to work. Can you suggest me a way to achive that? Another way to achive my goal could be to use a Worksheet_Change Event to UPDATE just the ActiveCell of workbooks(a) to workbooks(b) - but again my SQL is not sufficient to find a solution. Any help is appreciated, Michael
Hi Michael! I don't know if video 58.30 in this playlist is useful to you but it might be worth a look ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html I hope it helps!
HI Andrew, your amazing- I have a slight problem - at work i run a piece of SQL statement to create a table and then run a piece if SQL statement to get back data..now i want to be able to run these statements automatically through VBA - so this the SQL codes i run first. - Please note some if the functions used are built in functions my work place have created call master.drop_table ('drop table MI166_A') ; create table MI166_A as ( select /*+ PARALLEL(a,32) */ * from CIS.TVP068ACTIVITY a where TS_COMPLETED is null ) ; create index IDX_MI166_A1 on MI166_A (NO_ACCOUNT, CD_COMPANY_SYSTEM) ; create index IDX_MI166_A2 on MI166_A (NO_EMPL_ASSGN, CD_COMPANY_SYSTEM) Once this has been done i want to run another Select SQL statement immediately How can i do this in VBA to run in sequence?
In reply to my own comment above, the quadrupled quotation marks """" are definitely necessary for MySQL to handle strings with apostrophes in them. I don't know whether the same is true of Microsoft SQL Server. Also, I don't know why I thought it necessary to make the CommandType adCmdText and start the CommandText with "Call". All you need is With cmd .ActiveConnection = cxn .CommandType = adCmdStoredProc .CommandText = "MySPwithInputs(" & """" inputString1 & """" & "," & """" & inputString2 & """" & ");" End With followed by cmd.Execute or Set rs = cmd.Execute In my testing, setting a recordset with an ADODB.Command block like that is always faster than running Set rs = cxn.Execute("MySPwithInputs(" ... ");" To get an rs.RecordsAffected number other than -1, I've always had to make sure I specified cxn.CursorLocation = adUseClient, which cursor location is inherited by any recordset created from that connection, or rs.CursorLocation = adUseClient _before_ the Set rs... line.
Hi Jay, there are several ways to do this support.microsoft.com/en-gb/help/168336/how-to-open-ado-connection-and-recordset-objects It really doesn't matter which method you choose when you simply want to query a database. If you want to modify the lock type or cursor type of the recordset you'll need to use the Open method on the recordset object to do so. As to why there are so many ways of doing the same thing, your guess is as good as mine!
Found the videos (#30 - #32) very helpful but am now having a problem with generating command text over 255 characters. I am doing more research and have tried joining a text array and bypassing functions but to no avail. Is there no way to get around the 255 character limit for command text?
***** Thanks for reply and again for videos. I got sidetracked on some usergroups indicating that there was a limit and went through several iterations ultimately switching from late to early binding and from using command text to a recordset. In the late stages of getting the recordset method working and after reading your post this morning, I became aware that I had created a non-null field in a temp table I was trying to insert Excel records into and was not filling the field. I altered the command text that created the temp table to include field names which put it over 255 and it worked (still had incomplete and/or inconsistent text in VB editor when debugging as you mentioned). The recordset method of inserting my Excel records worked although I still want to see if I can get a command text version working now since I'm actually more comfortable with SQL and just learning VBA. Regardless, wasn't able to do any of this 72 hours ago so thanks one more time (and hope you've had time to see Gravity, great movie)
Inserting one record generates blank records and erroring out. The SQL string generates blank record set, only happens with one record to insert. I suspect its the Range.End(XLDown) function
hai Andrew, I am stuck with the execution of following INSERT query. "INSERT INTO TOLLYWOOD_ACTORS (ID,NAME,DOB,SKILLS,STYLEMETER)" & _ "VALUES (8, 'RaviTeja', '12-jul-1979', 'Acting', 8);" Please help me whats wrong with my query. I couldn't insert data into ORACLE database using this query. plz help.
I've been stuck trying to get past video time 19:00. When I run my code I get "Syntax error in INSERT INTO statement". I'm running this on office 2013 from VBA to Access DB. Please help. None of the solutions online are helping
***** Thank you for your response. The error message says: Run-time error'-21472179000(80040e14)': Syntax error in INSERT INTO statement I have looked up this error and mainly have read suggestions that its Access causing the issue. I even tried letting Access write the SQL statement like you showed but when I revert back I get this same error in Access too. Is there maybe something I forgot to enable in the reference library? I have the latest version of Access selected and I'm using MSFT Office 2013 just like you.
Is anybody getting the error: "3706 Provider cannnot be found " when connecting to Sql Server...I am doing it exactly like the tutorial but the connection doesn't want to open?
Hi Helena! It could be that you don't have the correct provider or driver installed. You may have more success using the more up-to-date version of this tutorial for SQL Server ua-cam.com/video/OWKae1pTTnE/v-deo.html Microsoft changed their recommendation for SQL Server connections a couple of years ago to the one described in that video. We have more videos that are newer and specific to SQL Server in the same playlist starting from Part 56.1 ua-cam.com/play/PLNIs-AWhQzclbRVLCZlsFvpz6fz2nPGbt.html I hope it helps!
I will thru the code thru f8 and get thru the execute part a dozen times then I will hit this error Run-Time Error '-2147217900 (80040e14)': Incorrect Syntax near 's'. I have looked it up everywhere and can't get an answer. Does anyone have any thoughts?
Hi Andrew, I love your tutorials! Although, I'm having a slight problem. I get an autimation error upon reaching .Open. My code looks like this(I'm tweaking it a bit for my own project, so the server names, user name and password have been ommited): Option Explicit Const SQLConStr As String = "Server=tcp:Azure Server Name.database.windows.net;Database=****;User ID=****@Azure Server Name;Password=****;Trusted_Connection=False;Encrypt=True;" Sub ConnectTODB() Dim OppsConn As ADODB.Connection Dim OppsCmd As ADODB.Command
Set OppsConn = New ADODB.Connection Set OppsCmd = New ADODB.Command
Well thank you either way! You at least have me searching in the right place. I figured it may be the string too, as I've had similar issues in creating the connection using php and Ajax. I gotta say though, I love your tutorials. Too many tutorials out there break the #1 rule of coding: don't repeat yourself.
+WiseOwlTutorials Thank you for the answer but i wanna modify the data (update,delete,insert) from created the userform. For example : I have a userform and there are 3 textboxes and 1 commandbutton..When i click the commandbutton then data will change based on values of textboxes.in your opinion is it possible ?
Why now so many people want to tell-all they know about excel vba ? Maybe, bcos they know vba is going to be stagnant and will be replaced by officeScript.
By far, the best instructor, most comprehensive knowledge on the subject matters. Thank you for sharing your gift & knowledge.
Have not seen new material lately. Hope you are well.
Andrew, I have not found any better teacher : You are the best!!
You're amazing Andrew!! I have been watching all your videos for the past year and I have grown exponentially in my coding capabilities! Keep up the amazing and work and THANK YOU!
haha you are a great teacher so it wasn't that cruel a punishment! They definitely have, please keep making more so i can keep on learning :p
Very Helpful! I've used these ADODB tutorial for sharepoint Lists. So good!
Happy to hear that you found the videos useful Diego, thank you for watching!
Excellent training videos ... you are well organized and provide good examples and are very articulate. I appreciate all the work you
put in towards making these videos.
Simply amazing Andrew! The detail you go through in each and every videos is really praise worthy. Also i really love the enthusiasm with which explain the concepts. :) you truly are a gem!
in other videos tutorials for any other subjects, i get easily distracted even if they are for a couple of minutes. but these videos i can watch for hours :)
Thanks so much Abhishek!
Wow Sir, this is an informative video which blows my mind and the content which is in this video is something which I even can't imagine especially drop statement in cells that's a fantastic superb
thank you so much sir again
You're very welcome Pradeep, thanks for watching!
Thank you very much for these tutorials! I've been watching them for days. btw, deleting the awful movie was very instructive xD
Great video. Was able to get it working on my database. Thanks.
Sir waiting for new videos upload on Excel VBA series. Please let us know by when new series would be uploaded.
I have learned a lot by watching your video. Thanks a lot.
Your Tutorials are awesome. This is from Bangladesh. Please add subtitle. I helps us to understand more. Again i say Your all movie are most usefull.
***** Wow! I am surprised to have seen this feature. Thank you so much. They are not great but very enough to understand.
Thank you sir for this series
Great job...Thanx very much!
Thanx very much
In the error handler you can clear the terror after the message and resume above the exits sib so that you close the connection in only one place and also exit only once
King! King! King! King! of VBA
Andrew, thanks for the proper video, very helpful.
I already implemented a query that writes multiple lines into an accdb file. Works brilliantly. I am now trying to modify the code to only insert new data. In sql it would be a code like: insert (x,y) values(x,y) where x not in (select distinct x from db). How do I implement it from excel ADODB? Do I need to use a recordset to select from the db before I can fill it with sql or can I add the Where statement? Thanks for a hint. Best, Michael
Hello Andrew, thank for these tutorials.I have a question, is possible incorporate na Application,InputBox in the VALUES?
So glad to have found your videos. They are excellent and thanks for sharing so much knowledge. I'm following along at home and getting a very odd problem around minute 41-42. My access database isn't letting me execute the first query in the loop: "INSERT INTO tblFilm (FilmID, FilmName, FilmReleaseDate, FilmRunTimeMinutes) VALUES (
(SELECT max(FilmID) FROM tblFilm) + 1, 'Marvel''s The Avengers', '2012-05-04', 143)".
If I change the subquery (SELECT max(FilmID) FROM tblFilm) it works just fine. And if I run the main query with a given number (999 for instance), the query also runs just fine. I didn't have any issues with the recordsets in the prior videos. It may be related: but I can't execute the queries into the Access database without a FilmID (that is, the autoincrement isn't working).
Any ideas what I'm doing wrong?
Thank you very much
You're very welcome Mark, thanks for watching!
Hi WiseOwlTutorials Awesome videos to learn more thanks a lot sharing.
i have one question: Are we able to connect the sql which is available in (IP Address) Server. if yes please let me know how we can do it???
Thanks in advance.
Grateful for the video, really helpful! Thank you!
Please be so kind and help me figure this out:
- lets say, instead of FilmTime as Integer, where numbers are whole, my excel table has decimal numbers. Data type in SQL is set to decimals (30,28, not null), so naturally FilmTime as integer is blocking my decimals..
My questions is, what data type in VBA should I set my FilmTime as? and what format in values? any suggestions?
Hi Ania! You could use the Double VBA data type to successfully pass values to a DECIMAL type in SQL. You might also find this reference table helpful docs.microsoft.com/en-us/office/vba/access/concepts/error-codes/comparison-of-data-types
I hope it's useful!
@@WiseOwlTutorials Thank you for a quick answer. Your suggestion worked as I formatted the FileTime value:
Replace(Format(FilmTime, "#0.00"), ",", ".") & ");"
I have another challenge to solve: my primary key 'ID' does not except duplicates. Is it possible to change if statement:
IF instr(1, SQLStr, "drop", vbTextcompare) 0
or add
IF NOT EXISTS statement before INSERT statement to check if there are any duplicates:
SQLstr= _
"IF NOT EXISTS (SELECT 1 FROM tblFilm WHERE FilmID = '" & FilmID & "') " & _
"insert into tblFilm (" & _
@@aniapirc9485 Hi Ania! Yes, indeed you can:
cmd.CommandText = _
"IF NOT EXISTS (SELECT TOP 1 * FROM dbo.Actor WHERE ActorID = 4053) " & _
"INSERT INTO dbo.Actor (FirstName, FamilyName, DoB) " & _
"VALUES ('Clever', 'Cat', '1992-06-24')"
I haven't concatenated the id value in this example but you can do so if you need to.
I hope it helps!
good morning wiseowl, thanks for the great video it was very helpful in learning using sql statements with activex data objects. I would like to ask how to code the insert query to be able to insert data in a single field coming from two textbox controls. just like concatenating the two controls to store in a single field in a database properly. I would appreciate your response regarding my query about the insert query.
Looking forward to your response- thank you
Hi! Can you tell how we can use SQL Stored Procedures with ADO? For example my DB name is DB_Common and my Stored Proc is named spUpdateVol, how would I use it with Command?
Hi Andrew,
your content is gold! I have learned more in 2 hours with your tutorials than in months on my own! The seemingly trivial, small things that you do are quite a discoveries to me (-I've been doing this wrong all this time....). I'm definitely donating you a few £ for these brilliant tutorials :)
One question though, if I would write a function which would query the database and return a recordset - how to go about closing the connection? I mean, if I close the connection within the function, before returning the recordset I invalidate the recordset, right?
Thank you very much!
Hi, Awesome videos on ADO... Made my life easier at work... Thanks for all of your Awesome videos... Are you planning on releasing videos on ADOX? And is ADOX better than ADO in excel?
Hi. Thanks for these helpfull tutorials. My question is about the passwords. How to enter password during opening connection with the access database with password protection?
Hey Andrew, you are simply awesome. Appreciate your effort in making these videos and helping us learn new things. I have one question - is there a way to convert Excel VBA macros into an exe file.
Hi Andrew,
Trust you are doing well.
Pls help me to know if we can automate any SAP based task with help of VBA.
Best regards,
Amit
Hi
Could you please tell me how to display the result of a SELECT query in excel sheet with ADODB.Command method?
I'd like something like .CopyFromRecordset method
Hi Andrew,
that’s a very helpful video - but unfortunatly I‘m still stuck with a problem:
After editing some cells in workbooks(a).worksheets(a) I would like to save the whole UsedRange of worksheets(a) to workbooks(b).Worksheets(a) using ADODB, this way saving a copy of worksheets(a) to workbooks(b) without opening workbooks(b) at all.
Both worksheets(a) are identical (columnheaders, UsedRange) in both workbooks.
I can copy the worksheet using rs.AddNew and rs.Update one time allright - but from then on I will just add the UsedRange again and again instead of updating the existing data. I‘ve tried the UPDATE command but cant get it to work.
Can you suggest me a way to achive that?
Another way to achive my goal could be to use a Worksheet_Change Event to UPDATE just the ActiveCell of workbooks(a) to workbooks(b) - but again my SQL is not sufficient to find a solution.
Any help is appreciated, Michael
Hi Michael! I don't know if video 58.30 in this playlist is useful to you but it might be worth a look ua-cam.com/play/PLNIs-AWhQzckUd5i0E1xeSFeBAonYIurK.html
I hope it helps!
HI Andrew, your amazing- I have a slight problem - at work i run a piece of SQL statement to create a table and then run a piece if SQL statement to get back data..now i want to be able to run these statements automatically through VBA - so this the SQL codes i run first. -
Please note some if the functions used are built in functions my work place have created
call master.drop_table ('drop table MI166_A')
;
create table MI166_A as
(
select /*+ PARALLEL(a,32) */
*
from CIS.TVP068ACTIVITY a
where TS_COMPLETED is null
)
;
create index IDX_MI166_A1 on MI166_A (NO_ACCOUNT, CD_COMPANY_SYSTEM)
;
create index IDX_MI166_A2 on MI166_A (NO_EMPL_ASSGN, CD_COMPANY_SYSTEM)
Once this has been done i want to run another Select SQL statement immediately
How can i do this in VBA to run in sequence?
Great videos! many thanks. Just wondering if you had stored procedures in MS SQL - how could you run them?
In reply to my own comment above, the quadrupled quotation marks """" are definitely necessary for MySQL to handle strings with apostrophes in them. I don't know whether the same is true of Microsoft SQL Server. Also, I don't know why I thought it necessary to make the CommandType adCmdText and start the CommandText with "Call". All you need is
With cmd
.ActiveConnection = cxn
.CommandType = adCmdStoredProc
.CommandText = "MySPwithInputs(" & """" inputString1 & """" & "," & """" & inputString2 & """" & ");"
End With
followed by
cmd.Execute
or
Set rs = cmd.Execute
In my testing, setting a recordset with an ADODB.Command block like that is always faster than running Set rs = cxn.Execute("MySPwithInputs(" ... ");"
To get an rs.RecordsAffected number other than -1, I've always had to make sure I specified cxn.CursorLocation = adUseClient, which cursor location is inherited by any recordset created from that connection, or rs.CursorLocation = adUseClient _before_ the Set rs... line.
Just a question ? how many yrs of experience do u have ?
im getting runtime error in adding subqueries in code
Subqueries are not allowed in this context, only scalar expressions are allowed.
???
Which one Shall I use , con.execute , or con.command why is there more then one way of querying the database ? And which one is the best
Hi Jay, there are several ways to do this support.microsoft.com/en-gb/help/168336/how-to-open-ado-connection-and-recordset-objects
It really doesn't matter which method you choose when you simply want to query a database. If you want to modify the lock type or cursor type of the recordset you'll need to use the Open method on the recordset object to do so. As to why there are so many ways of doing the same thing, your guess is as good as mine!
Found the videos (#30 - #32) very helpful but am now having a problem with generating command text over 255 characters. I am doing more research and have tried joining a text array and bypassing functions but to no avail. Is there no way to get around the 255 character limit for command text?
***** Thanks for reply and again for videos. I got sidetracked on some usergroups indicating that there was a limit and went through several iterations ultimately switching from late to early binding and from using command text to a recordset. In the late stages of getting the recordset method working and after reading your post this morning, I became aware that I had created a non-null field in a temp table I was trying to insert Excel records into and was not filling the field. I altered the command text that created the temp table to include field names which put it over 255 and it worked (still had incomplete and/or inconsistent text in VB editor when debugging as you mentioned). The recordset method of inserting my Excel records worked although I still want to see if I can get a command text version working now since I'm actually more comfortable with SQL and just learning VBA. Regardless, wasn't able to do any of this 72 hours ago so thanks one more time (and hope you've had time to see Gravity, great movie)
Inserting one record generates blank records and erroring out. The SQL string generates blank record set, only happens with one record to insert. I suspect its the Range.End(XLDown) function
Hello Andrew, I'm getting a Run-time error (You must enter a value in the 'tblfilm.filmID' field) at the line for MoviesCmd.Execute. Any suggestions
Hello Andrew, Yes it was with the access version. The filmID is not formatted as an autonumber. It is formatted as a number
Thanks for the quick reply. I had considered what you mentioned, so I will give it another shot!
hai Andrew, I am stuck with the execution of following INSERT query.
"INSERT INTO TOLLYWOOD_ACTORS (ID,NAME,DOB,SKILLS,STYLEMETER)" & _
"VALUES (8, 'RaviTeja', '12-jul-1979', 'Acting', 8);"
Please help me whats wrong with my query. I couldn't insert data into ORACLE database using this query. plz help.
how to create update query by using sql and that to take data from combo box eg. eid and based on eid change data can be updated on following text box
I've been stuck trying to get past video time 19:00. When I run my code I get "Syntax error in INSERT INTO statement". I'm running this on office 2013 from VBA to Access DB. Please help. None of the solutions online are helping
***** Thank you for your response. The error message says:
Run-time error'-21472179000(80040e14)':
Syntax error in INSERT INTO statement
I have looked up this error and mainly have read suggestions that its Access causing the issue. I even tried letting Access write the SQL statement like you showed but when I revert back I get this same error in Access too. Is there maybe something I forgot to enable in the reference library? I have the latest version of Access selected and I'm using MSFT Office 2013 just like you.
Is anybody getting the error: "3706 Provider cannnot be found " when connecting to Sql Server...I am doing it exactly like the tutorial but the connection doesn't want to open?
Hi Helena! It could be that you don't have the correct provider or driver installed. You may have more success using the more up-to-date version of this tutorial for SQL Server ua-cam.com/video/OWKae1pTTnE/v-deo.html
Microsoft changed their recommendation for SQL Server connections a couple of years ago to the one described in that video.
We have more videos that are newer and specific to SQL Server in the same playlist starting from Part 56.1 ua-cam.com/play/PLNIs-AWhQzclbRVLCZlsFvpz6fz2nPGbt.html
I hope it helps!
@@WiseOwlTutorials thanks for the reply!
@@helenaviljoen3375 No problem Helena, I hope it helps!
Why when I use insert into with ado all data when is store save like '8
I don't know, what does your code look like?
I wonder what will happen if the connection is not closed at the end?
***** make sence, ty man.
I will thru the code thru f8 and get thru the execute part a dozen times then I will hit this error Run-Time Error '-2147217900 (80040e14)': Incorrect Syntax near 's'. I have looked it up everywhere and can't get an answer. Does anyone have any thoughts?
'Setup Command Update
Dim BTcmd As ADODB.Command
Set BTcmd = New ADODB.Command
Dim r As Range
BTcmd.ActiveConnection = connex
For Each r In Range("A2", Range("A2").End(xlDown))
BTcmd.CommandText = Getinserttext(r.Value, r.Offset(0, 1).Value, r.Offset(0, 2).Value, r.Offset(0, 3).Value, r.Offset(0, 4).Value, _
r.Offset(0, 5).Value, r.Offset(0, 6).Value, r.Offset(0, 7).Value, r.Offset(0, 8).Value, r.Offset(0, 9).Value, r.Offset(0, 10).Value, _
r.Offset(0, 11).Value, r.Offset(0, 12).Value, r.Offset(0, 13).Value, r.Offset(0, 14).Value, r.Offset(0, 15).Value, r.Offset(0, 16).Value, _
r.Offset(0, 17).Value, r.Offset(0, 18).Value, r.Offset(0, 19).Value)
BTcmd.Execute
Next r
End Sub
Function Getinserttext(Invoice As String, Invoicestatus As String, DateRan As String, Branchname As String, PayorName As String, LastID As String, EndDOS As Date, DSO As Integer, Type1 As String, Gross As Integer, Net As Integer, CA As Integer, Payments As Integer, AR As Integer, Difference As Integer, Percentage As Integer, Secondary As String, Lastworkeddate As String, Daysworked As Integer, OpenDt As String) As String
Dim SqlStr As String
SqlStr = _
"INSERT INTO dbo.Dashboard (" & _
"Invoice,InvoiceStatus,DateRan,BranchName,PayorName,LastID,EndDOS,DSO,Type1,Gross,Net,CA,Payments,AR,Difference,Percentage,Secondary,LastWorkedDate,DaysWorked,OpenDt)" & _
"Values (" & _
"'" & Invoice & "'," & _
"'" & Invoicestatus & "'," & _
"'" & DateRan & "'," & _
"'" & Branchname & "'," & _
"'" & PayorName & "'," & _
"'" & LastID & "'," & _
"'" & EndDOS & "'," & _
"'" & DSO & "'," & _
"'" & Type1 & "'," & _
"'" & Gross & "'," & _
"'" & Net & "'," & _
"'" & CA & "'," & _
"'" & Payments & "'," & _
"'" & AR & "'," & _
"'" & Difference & "'," & _
"'" & Percentage & "'," & _
"'" & Secondary & "'," & _
"'" & Lastworkeddate & "'," & _
"'" & Daysworked & "'," & _
"'" & OpenDt & "')"
Getinserttext = SqlStr
I tried to put replace in the Payor name because some payor names have ' in them but I kept getting a type mismatched error message.
Thank you, I love your videos.
Hi Andrew, I love your tutorials!
Although, I'm having a slight problem. I get an autimation error upon reaching .Open. My code looks like this(I'm tweaking it a bit for my own project, so the server names, user name and password have been ommited):
Option Explicit
Const SQLConStr As String = "Server=tcp:Azure Server Name.database.windows.net;Database=****;User ID=****@Azure Server Name;Password=****;Trusted_Connection=False;Encrypt=True;"
Sub ConnectTODB()
Dim OppsConn As ADODB.Connection
Dim OppsCmd As ADODB.Command
Set OppsConn = New ADODB.Connection
Set OppsCmd = New ADODB.Command
OppsConn.ConnectionString = SQLConStr
OppsConn.Open
OppsCmd.ActiveConnection = OppsConn
OppsCmd.CommandText = GetInsertText
OppsCmd.Execute
OppsConn.Close
Set OppsConn = Nothing
End Sub
Well thank you either way! You at least have me searching in the right place. I figured it may be the string too, as I've had similar issues in creating the connection using php and Ajax.
I gotta say though, I love your tutorials. Too many tutorials out there break the #1 rule of coding: don't repeat yourself.
How can i pull data into a userform ? (Not sheet).Is there anyway ?
+WiseOwlTutorials Thank you for the answer but i wanna modify the data (update,delete,insert) from created the userform.
For example : I have a userform and there are 3 textboxes and 1 commandbutton..When i click the commandbutton then data will change based on values of textboxes.in your opinion is it possible ?
***** I'll try.Thank you for your support.
🥰🥰🥰🥰🥰🥰
Why now so many people want to tell-all they know about excel vba ? Maybe, bcos they know vba is going to be stagnant and will be replaced by officeScript.