using phpmyadmin...need to somewhat sync access (1 Viewer)

Toadums · Jul 20, 2009

So Im not sure how I am going to go about doing this...I want a macro that will pull data off my sql server.

I know that there is the odbc option under the import...but that seems to only work with sql servers that are on the same machine..not web based sql servers (im not sure if thats what mine is, but it doesnt work with phpmyadmin, what ever that is)

...The only way that I get this to work is to go to PHPmyadmin and export my database as an xml file, and then import the xml file into access...works well...except you know high up executive type people, they hate doing stuff on their own, so i need to write some code that will connect to my database on its own...or some how automate the whole downloading process...

is this possible? any questions feel free to ask.

dfenton · Jul 21, 2009

I'm having trouble understanding your question. phpMyAdmin is for administering MySQL only. It does not work with any other database engine.

Secondly, there should be no issues with connecting to a SQL Server (or any database server) on another computer using ODBC. You just need to define a DSN that points to the other server and then connect to that. Once you've done that, you can use Doug Steele's code to convert DSNs into DSN-less connection strings:

http://www.accessmvp.com/djsteele/DSNLessLinks.html

Then your app can be distributed to computers that have no DSN defined for the remote server. But you'll still find it hand to keep the DSN defined on the machine you are developing on, as structural changes to table/view can require you to recreate your links.

Toadums · Jul 21, 2009

Yeah...I dont know my terms for this stuff...lol...and im not sure exactly what phpmyadmin is.

I get stuck here when i try to create a odbc connection. i hit new DNS name, enter SQL server, then select next and I get here.

Also in the screenshot i have a picture of what my sql interface looks like. I get this sql server via Dreamhost when I signed up for a domain, and place to host files.

let me try to rephrase my question..or atleast restate it:

I would like to have a somewhat automated connection between my sql server and my access 2007 database...it looks like odbc is exactly what i want??

Thanks for your answer so far! I will read the link you sent me

**EDIT** I just downloaded and connected to my sql server with SQL yog...if that makes any difference? makes anything easier. this program is saved to my computer atleast...?

dfenton · Jul 22, 2009

What you need to do is set up a DSN that points to your server. If it's accessible only via HTTP, you're out of luck. You'll need an IP address and port number (if it's not on the default port).

I would suspect that this is never going to work, as I don't think Dreamhost is going to expose your SQL Server port to the Internet by default.

Toadums · Jul 22, 2009

Ok. thanks for the info! I guess I will just try my other alternative, and set up a php page that downloads and saves the sql database as xml, so that I can import it into access.

Banana · Jul 22, 2009

Just to clarify something... All HTTP address are resolvable into IP address. I don't know how Dreamhost usually does it, but I would be very very surprised if they didn't have a server with a static IP address for hosting MySQL, and they usually give you the information for connecting to that server when you sign up.

Edit: From what you've described, it really sounds like you are using MySQL, not SQL Server (which is a specific product made by Microsoft & Sybase and not really a good generic name for any SQL database product) Are you absolutely you meant SQL Server? SQLyog is MySQL-only AFAIK.

Toadums · Jul 22, 2009

not sure what I use exactly...on the screen it has possibly the information...

MySQL client version:5.0.321 ... never meant to say i was using sql server >.< lol!

Ok. so from what you are saying, i should be able to get the DNS set up for my online mysql thingy? i can do this in access? I dont see where (in setting up am odbc connection) it says anything about ip's or anything..?

edit: i found an IP for my mysql server or something...i found this in my control panel off the dreamhost website:

mysql A 69.163.190.106
how can I use this with access?

Banana · Jul 22, 2009

You need to have MyODBC driver installed, which you can download from mysql.com. The current version is 5.1 although I've seen some people report 3.51 driver as better so it wouldn't hurt to try both. Once you install the driver, it should be in the list when you create a new ODBC DSN; use that driver then when it prompts for more details such as server, database, username and password, fill in the IP address in the server portion, and give the name of database as well as your username & password. That'll get you started.

A caution: I don't know anything about Dreamhost and want to be sure you are aware- if the IP address you saw is not static, the DSN will break every time the IP changes. Again, I would be very surprised if Dreamhost allowed such setup but you never know!

Did this help?

Toadums · Aug 7, 2009

Banana said:
Did this help?

Sorry it took me so long to respond/test, i just got back from vacation yesterday...

but anyways THANK YOU!!!!!!!! works great! and at no point asked me for an IP address, so hopefully that obstacle has been avoided, lol. and a uto updates my access db when ever any changes are made in my sql server

oh. one quick question. if someone were to use this database on a different computer would they have to download the driver aswell? or is it sort of...saved in the access database, so all I have to do is email them the database..??

Banana · Aug 7, 2009

Glad you've made some progress.

Yes, the driver needs to be distributed & installed on every computer where the Access database will be used.

Toadums · Aug 7, 2009

Banana said:
Glad you've made some progress.

Yes, the driver needs to be distributed & installed on every computer where the Access database will be used.

ok, thats not too bad. will I have to set up the OCDB connection on every computer, or will that be saved within the access db?

Banana · Aug 7, 2009

Depends. If you used DSN-less connection and that was defined somewhere in the Access database, then no. If you used DSN, then you have to distribute that as well, which is a PITA and why I usually opt for a DSN-less connection.

Toadums · Aug 7, 2009

oh. i think I am using a DSN conection (I hit the New.. button next to DSN Name combo box)

shouldnt be a big deal tho...it will only have to go onto 2 or 3 machines, so I can walk them thru it, or go over to their houses and do it for them.

thanks again for the info!

dfenton · Aug 8, 2009

I use a DSN for development, and then run Doug Steele's code to convert the links to DSN-less links:

http://www.accessmvp.com/DJSteele/DSNLessLinks.html

Atomic Shrimp · Sep 4, 2009

Never buy anything from a spammer. Reported

using phpmyadmin...need to somewhat sync access (1 Viewer)

Toadums

Registered User.

dfenton

AWF VIP

Toadums

Registered User.

dfenton

AWF VIP

Toadums

Registered User.

Banana

split with a cherry atop.

Toadums

Registered User.

Banana

split with a cherry atop.

Toadums

Registered User.

Banana

split with a cherry atop.

Toadums

Registered User.

Banana

split with a cherry atop.

Toadums

Registered User.

dfenton

AWF VIP

Atomic Shrimp

Humanoid lifeform

Similar threads

Users who are viewing this thread