0

Update: I ran a SQL connection test function in my VBA and it reads open from my main workstation, I ran the same connection script at the client workstation and Runtime crashed. I also checked to make sure it was using a 32-bit rather than 62-bit driver and that the credentials were identical to another station that does work.

If I run a connection test Runtime crashes, if I try to only run the query it says the connection can't be found. Both attempted from the user computer.

I successfully opened an ODBC Unicode connection to the MySQL database from the client computer at System DSN level. When I open the Access Runtime file and try to use a form to query the database I receive the error: "ODBC--connection to 'servername' failed." I have tried numerous names including changing the case sensitivity. I verified that the TCPIP address I used was indeed to the host for this database, and that the name using ipconfig /all - was of the appropriate case sensitivity. I have not been able to figure out if it is an issue with Access vs. Runtime, but I can't really see that being the problem here. The name of the table is "tbl_panel," and it is definitely within the database I connected to with ODBC with that exact name.

The user requirements for the connection I used has basically "Read-Only" privilege, but that is all it should need as I am only checking the data. Unless creating a recordset is beyond the scope of SELECT, SHOW VIEW, CREATE TEMP TABLES. Furthermore, the fact that it can't find the server itself tells me its probably not to do with my SQL/VBA coding. Hovering over the tbl_panel in the Navigation Pane of Access shows "ODBC;DSN='servername';;TABLE=tbl_panel".

Here is the SQL string for creating the record set (truncated for space since the statement itself works fine):

stSQL1 = "SELECT tbl_panel.PNL_SN_ID FROM tbl_panel " & _ etc. Set qryList = dbsInspect.OpenRecordset(stSQL1)

This has me pretty stumped, and I am a rookie when it comes to ODBC, so if it is something obvious please be kind. I did do a lot of searching, but most ODBC queries return issues with the initial setting up of the Data Source, or opening the connection in code. Is that a possibly for what I have to do? Include an opening statement for the table in VBA so that Runtime knows what to do? I'm going to feel silly if that is what the problem likely is and I typed all this for nothing.

Adam
  • 36
  • 1
  • 11
  • if you only want to give the user ReadOnly privilege to that table then open the recordset as a snapshot `dbs.openrecordset(stSQL1, dbopensnapshot)` – geeFlo Mar 29 '17 at 18:51
  • Why not use linked tables which you build from ODBC connection (see External Data tab on Ribbon)? Once you link table, simply bound form to that table. – Parfait Mar 30 '17 at 01:33
  • The user should only have ReadOnly privilege already. I don't believe that's the problem. The table is already linked in the database using that method, but when I try to open it through a Runtime computer it tells me that the server cannot be found. – Adam Mar 30 '17 at 13:01
  • `DSN='servername'` won't work. Your question is a bit confusing, have you created the System DSN on the user computer? Then the DSN name (not server name) goes into the connect string. – Andre Apr 03 '17 at 15:32
  • @Andre The System DSN is set up on the user computer. The name for the DSN is the same as the server's name if that helps clear up the confusion. – Adam Apr 03 '17 at 15:57

1 Answers1

0

This ended up being a bit issue. Despite using the ODBC connection tool in Control Panel --> Administrative Tools. I needed a 32 bit connection. This is answered in another SO question.

HOWEVER, I will note that the only way I ended up getting an useful error message was by creating a backend. Then it displayed the error message in the linked question below:

Related Question on SO

Community
  • 1
  • 1
Adam
  • 36
  • 1
  • 11