Linking to SQL
- Thread starter burrelly
- Start date
We have a few databases that have been ported over.
You need to set up your odbc links and add a User DSN connection to the database in the first place.
1) Go to Control Panel, Administrative Tools, Data Sources (ODBC)... Click add select SQL Server Driver and follow the prompts..
2) Right click in the tables window and choose link tables, choose ODBC Connection from the dropdown box and select your database from the list.
Hope this points you in the right direction
Regards - Ian
You need to set up your odbc links and add a User DSN connection to the database in the first place.
1) Go to Control Panel, Administrative Tools, Data Sources (ODBC)... Click add select SQL Server Driver and follow the prompts..
2) Right click in the tables window and choose link tables, choose ODBC Connection from the dropdown box and select your database from the list.
Hope this points you in the right direction
Regards - Ian
boblarson
Smeghead
- Local time
- Today, 03:51
- Joined
- Jan 12, 2001
- Messages
- 32,040
Once the ODBC is set up then you use the Linked Table Manager to link to the backend. Make sure to select the file type of ODBC from the dropdown when the dialog comes up when you select TOOLS > DATABASE UTILITIES > LINKED TABLE MANAGER (for Access 2000-2003) or DATABASE TOOLS Ribbon selection and then the DATABASE TOOLS group on that ribbon.
Will this method work with MySQL databases?
Ok if I get that working on my local machine. I want to roll the front end out to around 250 users. Will i have to install and configure the drivers on all the systems? Is there an easier way to do it?
I have seen some stuff about DSN-less connections, does this still rquire the mysql driver to be installed on all the systems?
Also some of my tables have access 2007 attachment options I take it these will not work with mysql?
I have seen some stuff about DSN-less connections, does this still rquire the mysql driver to be installed on all the systems?
Also some of my tables have access 2007 attachment options I take it these will not work with mysql?
boblarson
Smeghead
- Local time
- Today, 03:51
- Joined
- Jan 12, 2001
- Messages
- 32,040
Yes, you will.
Not that I'm aware of.
Yes, it does
I haven't used MySQL, so I can't answer that one. I don't know if that is supported or not.
I distributed MyODBC along with my front-end application (and a library to boot) by using a freeware Install Creator and having it executing a batch file that was included which then automatically installed the MyODBC driver. The user had to click "Next" & "Finish" as I didn't have time to research whether I can install it without user's intervention, but at least the user didn't have to start the install process and the batch file made sure everything was there.
I also use DSN-less connection, but this still needs a driver.
EDIT: Just saw your question about attachment. I'm going to assume 'no' as this is too new and I don't know if it has been mapped to ODBC's BLOB datatype. You could at least test it and if it isn't mapped correctly, write up some routines to get it converted into BLOB, which MySQL supports.
I also use DSN-less connection, but this still needs a driver.
EDIT: Just saw your question about attachment. I'm going to assume 'no' as this is too new and I don't know if it has been mapped to ODBC's BLOB datatype. You could at least test it and if it isn't mapped correctly, write up some routines to get it converted into BLOB, which MySQL supports.
I have managed to set up the MySQL and everything seems to be working correctly the problem I am facing is the MySQL is hosted on a server within the internal network. User who are working in the office can access is.
I can access the database externally using the external IP.
A few of the users work from both the office and home. When I try to connect to the database using the external IP from internal it will not connect. It works fine when the user is out of the office.
So how do I get around this without having to keep changing the database source from an internal ip to an external?
I can access the database externally using the external IP.
A few of the users work from both the office and home. When I try to connect to the database using the external IP from internal it will not connect. It works fine when the user is out of the office.
So how do I get around this without having to keep changing the database source from an internal ip to an external?
If you don't mind using DSN-less connections, what I would do is incorporate a functionality to ping a server using the internal IP. If there is no response, use external IP. You can substitute the appropriate IP in the connection string after you've pinged the server then execute the connection string with a IP that is known to work for a given circumstance.
I believe it should be also possible to use external IP even from internal network but am guessing that your IT folks blocked it, and for a good reason, too. This is why I suggested using DSN-less connection so you can change the IP at runtime. But if that is not what you want, the only answer is to talk to your IT folks. One possible workaround is to provide a hostname that is available from both IPs so you only need to supply a hostname (e.g. mycompany.servername.com) and let the DNS (Domain Name Server, not DSN!) resolve it for you.
HTH.
(BTW, just checking- you are aware of security implications with connecting to server via WAN, correct?)
I believe it should be also possible to use external IP even from internal network but am guessing that your IT folks blocked it, and for a good reason, too. This is why I suggested using DSN-less connection so you can change the IP at runtime. But if that is not what you want, the only answer is to talk to your IT folks. One possible workaround is to provide a hostname that is available from both IPs so you only need to supply a hostname (e.g. mycompany.servername.com) and let the DNS (Domain Name Server, not DSN!) resolve it for you.
HTH.
(BTW, just checking- you are aware of security implications with connecting to server via WAN, correct?)
Last edited:
Cool.
So you found a satisfactory solution for the external/internal IP issue?
Nope that is not going to work all though I cant connect to the database I get a ping reply from the external ip internally
I bet it has to do with the port 3306 being blocked. Are you sure that the port 3306 is open to the external IP where you would connect from?
To test this, try to connect to the MySQL box externally using this site: canyouseeme.org
To test this, try to connect to the MySQL box externally using this site: canyouseeme.org
When on a pc on site I can ping the external IP address. I can connect to the MySQL data base using an internal ip 192.168.1.1 port 3306 this works with the ODBC, when i try to connect to to the external ip from an internal machine using the ODBC I get the following error:
Connection Failed: [HY000][MySQL][ODBC 5.1 Driver]Can't connect to MySQL server on 'external ip'(10061)
The router points 3306 to the internal machine and I can connect to the database from home. I have disabled the firewall to try that no difference.
The network is set up on an sbs server 2003 with active directory. SQL is on a seperate box not connected to the domain.
Similar threads
- Question
