I have an Excel Sheet that has MySQL Selects, Updates and Inserts. I want to run them from here to save time.
I have an online MySQL database hosted by TSO hosting.The Queries need to be able to get a result,
either the result of the SELECT or a confirmation that the UPDATE or INSERT was successful.
I found this if any use:
Please Login or Register to view this content.
Last edited by Michael Island; 09-21-2020 at 09:08 AM.
more than likely, this:
COULD be of some use, however what it looks like you did was simply copy it from the internet. Did you? and if you did, do you know what it is supposed to do?Please Login or Register to view this content.I say that because this code:
makes absolutely no sense. and the reason is because connections have to be qualified with something. e.g. a type of connection. just telling the coding environment that you want a connection in VBA will not work, I don't believe. I am referring specifically to the ever-so-popular library in windows called ADO (active data objects). either that or activeX data objects. it's one of those 2. and the most popular connection concept is found in the VBA language by using that library I think. furthermore, to connect to web-based databases, you need a heck of a lot more specs than just a servername, DB name and credentials, more than likely. take a look at the 2nd answer given here on SO:Please Login or Register to view this content.
https://stackoverflow.com/questions/...mysql-database
that person is using ADO like I just mentioned, plus he is using a port and specifying the ODBC driver information that is needed to point to MYSQL as well. all of that would more than likely be needed in your case too. and don't forget to declare an ADOBD recordset like he is doing too. just saying:
isn't going to do anything. or if it did, it's not the secure and/or professional way to write code. see below images for some help as well.Please Login or Register to view this content.
Hi Adam,
Yes I copied the code from a forum on the internet - this forum
I looked around and found a promising thread that should let me know how I might do it.
I am the Admin of the account on TSO hosting and the DBA so I know that they will give me the connection settings we need.
So what is my first step?
if you are admin of a hosting account, that's not going to make any difference. FYI. if you have the title of DBA, then if you indeed need to get information from the hosting provider that you need, obviously you will be authorized. However, every hosting compay in the world, at least to my knowledge, if you're paying them for their services, give you everything you need in terms of the information to get virtually anything done with any product you've purchased from them. so, in your case, you'll obviously need the MYSQL information for the purpose of connecting to it, if it is indeed possible. Now, you should know if you don't, that attempting to make a connection like this between a language that is 30+ years old and an up-to-date and secure platform like MYSQL might be a hair splitting effort. and the reason is because VBA was manufactured by MS and they haven't cared about it for the last 20 years or more because they make no money with it. and MYSQL of course is owned by Oracle, and they are not MS.Originally Posted by Michael Island
so, aside from my rambling, what I would do if I were you is gather the following info first:
=> ODBC data (connection string syntax and/or connector info for MYSQL and its version you are running).
=> any child info of the all-encapsulating mysql platform. e.g. - if you are using MARIADB.
=> all of the information you have already posted, plus what I have previously posted.
=> anything else that I did not mention, that your hosting provider suggests.
you should know though, that based on my 20 years of doing this, that hosting company's generally do not know what they're talking about. and I've worked with 7 different ones. that doesn't apply to everyone. there are good and bad apples. so it's up to you to use your own brain and discern the competence level of who you're talking to when you call them/chat with them. hosting companies manage massive networks and millions of servers, so the people on the phones really aren't privy to any knowledge other than knowing how to click buttons on the screen. the software does the work for them. however, I've talked to many level 1 techs that are 1000% more competent than their managers. so it does happen.
you may also want to look at the following thread, which gives good back-and-forth info between me and another gentleman from Europe. He attempted to connect MS ACCESS to another DB platform called pervasive by way of using PHP code. It never worked out for him, because there ended up being too many variables involved in the process and he nor I had the time to figure it out:
https://www.access-programmers.co.uk...string.309259/
to get MYSQL ODBC connector info, see any of these:
https://dev.mysql.com/doc/connector-...ndows-5-2.html
https://dev.mysql.com/doc/connector-...formation.html
https://dev.mysql.com/doc/connector-...tallation.html
https://dev.mysql.com/doc/connector-odbc/en/
Last edited by vba_php; 09-19-2020 at 09:24 AM.
Hi Adam,
This looks plausible:
I have informed TSO Hosting Support of what I am hoping to do - now to await their response.Please Login or Register to view this content.
Will seeing my little Excel Workbook help? I can attach it - it might give you more context of what I am trying to do?
Last edited by Michael Island; 09-19-2020 at 12:11 PM.
go ahead. I host many mysql DBs myself, so I may even be able to run a test on my end and verify that you are doing the right things on your end.
furthermore, this code:
might work for you. you are using the correct delimiter in between the specs, which, when you're ever dealing with ODBC connectivity, always is: the semicolon ( ; )Please Login or Register to view this content.
the rest of the code also looks OK, although I would have to verify:
are you sure that the recordset object has an open method? that's what you're doing. also, have you set the ADO reference in your libs? Considering that the code you posted is in proper case English, it's obvious that you copied it directly from Excel into your post.Please Login or Register to view this content.
Also, have you done anything in your DSN dialogs in windows? Most of the time you need to use that in cases like this as well. you would add your sources. see below images:
Hi Adam,
Will need guidance as to what I need to do.
We'll do this step-by-step.
The first step is that you see my, VBA-free, Excel workbook - I've got a DBA friend of mine looking for those connection settings from TSO hosting.
I am the Secretary for a Scale Model Society and it seems like Users want to Register at our annual competition offline rather than the website I created for them. So to make this possible, as the venue has wifi, we will do it via an Excel. Workbook attached.
welcome back Michael. So, if you have to wait on your DBA friend, is there anything else you wish to discuss right now? I looked at your file, but it doesn't mean anything to me
Last edited by vba_php; 09-20-2020 at 07:17 AM.
Sorry, Adam, but this comment is just not on: this is a free forum, and there should be NO suggestion of payment being required, either by you or anyone else. If you don't wish to offer help, that's fine, but don't suggest that you might if someone offered to pay you. If that's not what you meant, then you worded your comment very poorly.advanced knowledge should be paid for, and I request the same.
Please stop making comments about what you think the moderation team may or may not do here. Off topic discussions are welcome: we have the Water Cooler section for that. However, MySQL can be discussed in this forum section.Discussions with you about MYSQL features and engines will probably be watched by the authorities here and they would probably mention that it was off topic.
Last edited by AliGW; 09-20-2020 at 06:54 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
then the comment was worded poorly. I'm not requiring to be paid. I think I mentioned that in the last post. isn't there a forum section here where people can post employment opportunities? furthermore, I modified the comment so it now doesn't sound like I'm asking for money.
understood, Ali. I made the comment because I've heard complaints from authorities on so many other forums about ""veering off topic"". And there are many others. If that is not the case here, you folks are certainly in the minority. I won't step outside the boundary again and assume anything about you, any other mod or the admins like Dibbins.
The Commercial Services section is where members can pay for one-to-one assistance when they don't wish to do any of the work themselves: they set a brief. You will not have access to that area as a helper until you reach Expert status.isn't there a forum section here where people can post employment opportunities
No, we dont have anything like that, nor do we advocate requests or solicitations for employment - if you meant real, full-time jobs...if thats not what you meant, again that could probably have been worded better.
As Ali mentioned, we have a forum called Commercial Services, where members can pay to have specific questions answered, for an agreed-to fee. Access to that forum is granted only to Gurus' mods and admin, in other words to senior members who have earned their stripes on the forum
Thanks for changing the wording on your post #8, it reads much better - although I dont entirely agree with that sentiment. We often offer very advanced assistance here, completely free of charge
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hi Adam,
This might help you understand. I created a Website in the last 5 months ( www.gibscalemodelsociety.com ) and made a prototype of an Online Registration Form in VB in Excel. As My Competition was rapidly approaching and my Developer friend got ill, I decided to postpone the creation of this page until next year.
This meant that Registration will be done manually this year. So, I would Query the DB from this Excel we are creating. Since the venue has WiFi we can create an Excel that connects to, and Queries my DB. I know the SQL queries on it work. Is it worth it to create this Excel when I can just connect to the DB online?
Anyway, I’ll explain the registration process. A Competitor fills in the attached Registration Form. There are some fields he cannot fill but GSMS can: Registration number to identify the form and Competitor Registration and the Entries within it. Entry Model Number which is recorded on our DB against the Registration Number of the form gives a direct link to the artist name.
The attached image might help you:
############################################
Last edited by Michael Island; 09-21-2020 at 08:41 AM.
that is exactly what we are trying to accomplish in this conversation.
what do you mean by this? you mean you've actually RUN SQL queries on your server-side database from excel and verified that you get return data out of it?
NO, not really. no one in the world uses excel for the purpose that a web app is supposed to serve. not to mention the fact that HTML5 has a SLEW of features available in it that make online registration forms, what everyone in the world is calling, AWESOME. or, if you want the employee-based equivalent to that word, BEAST MODE. LOL.
as a follow up to all of this, yes I did look at the form you have, and, given all of the information you want registrants to put in and then some, you can do everything with a server DB, regardless of what it is. you can use all sorts of code to connect to it as well. I assume you have learned something from your developer friend that fell ill? for connection purposes between a web app and a server DB, you can use all of the following technologies:
=> AJAX (like the fetch() function)
=> PHP/MYSQL
=> ADODB in the .NET platform
also, I want to point something else out. on your website, you have a search icon in the upper right corner. when one clicks on that, you are bringing up a popup window with one long box in it for the search. I think that's nice, but I've never seen it done. Usually the search bar just appears at the top of the page and the rest of the content remains visible to the user. furthermore, on your search page, the background animated GIF image with the assigned class called search-curtain animated show is not functioning, nor is it visible (to me, using firefox anyway). behind the search icon you have and href of the # sign only. not like a typical page, maybe like this?
https://www.godaddy.com/help
I just thought I would throw that ""thought"" out there for you. So, let us know how else we can help, given everything I've just said.
DON'T post credentials. please delete those immediately.
so, you are obviously using wordpress, considering what TSO has given you. have you ever worked with wordpress before? you can literally do anything with it, and it's very easy, at least from what I've seen.
Hi Adam,
What I mean is that I have run all those SQL Queries Online on the DB and they work fine.Originally Posted by Michael Island View Post
I know the SQL queries on it work.
what do you mean by this? you mean you've actually RUN SQL queries on your server-side database from excel and verified that you get return data out of it?
Considering an excel SQL Query Creator and I running Queries online on the DB. I can use excel to build the query I need quickly.
Since the queries are already done its just a matter of meddling with Excel.
OK, so where does that leave us? do you have other questions for folks here?
OK, decided to play around with the Excel sheet. Will close this thread and add to your reputation.
I don't care too much about reputation, but thanks. You're certainly welcome to post again if you get stuck. Dibbins has said in this thread that many here offer help on other subjects outside of Excel. myself included.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks