Windows Server

Steve Schapel · Guest

Hi. Please re-direct me if there is a more suitable newsgroup for this
question.

On a client's site, we have an Access database, from which we have a
scheduled task updating the data in a MySQL database on a web server,
via an ODBC connection.

This has been running with no problem on a Windows 2000 Server for a
couple of years.

A few months ago, a SP4 Security Rollup package for Win 2k was applied
to the server, following which the databasse update failed. After
trying "this, that, and the other", in the end we "resolved" the problem
by backing out of the rollup.

Ok, now they have installed a new Windows 2003 Server (SBS), and the
same problem has occurred. The ODBC connection to the remote server is
successful. The routine is supposed to do this:
- delete backup tables from the web server database
- rename tables in the web server database to backup tables
- export data from the local database to the web server database

The first two steps occur successfully, but the data export is simply
not happening.

Any ideas as to what might be happening? Thanks.

--
Steve

Dave Patrick · Guest

What happens if you open the access database can you view each of the linked
table's data? Some of the tables may need to be relinked.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Steve Schapel" wrote:

Dave Patrick · Guest

I like to ADD this trap in to raise the SQL error.
http://support.microsoft.com/default.aspx?scid=kb;en-us;154510

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Steve Schapel" wrote:

Steve Schapel · Guest

Thanks a lot, Dave, I will give this a try and see what it comes up
with. Your help is much appreciated.

--
Steve Schapel, Microsoft Access MVP

Dave Patrick wrote:

Dave Patrick · Guest

You're welcome. Adding that trap has helped me resolve a few issues in the
past.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Steve Schapel" wrote:

Steve Schapel · Guest

Dave,

Thanks a lot for the reply. Yes, the Access database is correctly set
up, and before the export of the data it runs a number of Make-Table
queries to compile the data into suitable configuration for the MySQL
tables, and this is also functioning correctly. I am sure it is in the
data transfer process itself that the holdup is happening, i.e. sending
the data out from the local server (via Port 3306) is not being
permitted, even though (as far as we can see) the security settings and
permissions etc have been set up appropriately.

--
Steve

Dave Patrick wrote:

Steve Schapel · Guest

Ok, I have worked further on this. There is no error at all being
raised here. The ODBC connection itself tests ok, and it is obviously
connecting from within the Access app, as some parts of the routine
(remote tables are getting deleted and renamed) are happening
successfully. But the part of the process where data is supposed to get
uploaded to the MySQL database just isn't happening, but with no error.
It used to work ok, and it still works ok from my own computer. Also,
when you go to File|Get External Data in Access, and try to connect to
the data source, it used to show you the tables in the remote database,
but no longer does so (the Import Objects or Link Tables dialog just
doesn't happen). This has been the result of a change from Windows 2000
Server to Windows Server 2003, but the system admin guy can't find any
explanation. Any further thoughts?

--
Steve Schapel, Microsoft Access MVP

Dave Patrick wrote:

Dave Patrick · Guest

You might have a look in the security log on 2003. It may be a firewall
issue. Can you turn it off temporarily to test?

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Steve Schapel" wrote:
| Ok, I have worked further on this. There is no error at all being
| raised here. The ODBC connection itself tests ok, and it is obviously
| connecting from within the Access app, as some parts of the routine
| (remote tables are getting deleted and renamed) are happening
| successfully. But the part of the process where data is supposed to get
| uploaded to the MySQL database just isn't happening, but with no error.
| It used to work ok, and it still works ok from my own computer. Also,
| when you go to File|Get External Data in Access, and try to connect to
| the data source, it used to show you the tables in the remote database,
| but no longer does so (the Import Objects or Link Tables dialog just
| doesn't happen). This has been the result of a change from Windows 2000
| Server to Windows Server 2003, but the system admin guy can't find any
| explanation. Any further thoughts?
|
| --
| Steve Schapel, Microsoft Access MVP

Dave Patrick · Guest

Someone should also be able to examine the firewall logs when your
application fires.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Steve Schapel" wrote:
| Thanks again, Dave. I have asked their admin person about temporarily
| disabling the firewalls (apparently there is a router one and a server
| one), so we can test. I'll let you know.
|
| --
| Steve Schapel, Microsoft Access MVP

Steve Schapel · Guest

Thanks again, Dave. I have asked their admin person about temporarily
disabling the firewalls (apparently there is a router one and a server
one), so we can test. I'll let you know.

--
Steve Schapel, Microsoft Access MVP

Dave Patrick wrote:

Steve Schapel · Guest

Thanks, Dave. We tried disabling the firewalls and it is just the same
as before.

--
Steve Schapel, Microsoft Access MVP

Dave Patrick wrote:

Dave Patrick · Guest

You might check for an updated mysql driver. http://dev.mysql.com/

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Steve Schapel" wrote:
| Thanks, Dave. We tried disabling the firewalls and it is just the same
| as before.
|
| --
| Steve Schapel, Microsoft Access MVP

Steve Schapel · Guest

Here's a weird thing... From the Access database on the server in
question, I can't use the normal procedure to link to the tables in the
remote MySQL database. But, if I use the Access database on my computer
to establish linked tables to the remote MySQL database, and then upload
the Access database to the problem Win 2003 server, guess what... the
table links are retained, the linked tables are accessible, allowing
deletes and writes etc. Doesn't make sense at all to me how this could
happen. But I am going to try and re-write the procedures in the
database to use Insert queries into the linked tables, rather than table
transfers. See if that solves it.

Thanks for the suggestion about updating the MySQL ODBC driver, however
we already are using the latest GA release.

--
Steve Schapel, Microsoft Access MVP

Dave Patrick wrote:

Dave Patrick · Guest

Try creating a new file *.udl, then open, create a connection to the
database using the same driver and credentials and finally 'Test'
connection.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Steve Schapel" wrote:
| Dave,
|
| What I meant by this was that normally, from within Access, when you go
| to the File|Get External Data menu, either Link or Import, select ODBC
| Databases, and and try to connect to the data source, it shows you the
| tables in the remote database. But on the "problem machine" it no
| longer does so - you get to the point of selecting the DSN, and then the
| Import Objects or Link Tables dialog just don't happen, just nothing.
|
| --
| Steve Schapel, Microsoft Access MVP

Dave Patrick · Guest

"I can't use the normal procedure to link to the tables in the remote MySQL
database"

What happens when you try? Are you logging on with the same credentials?

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Steve Schapel" wrote:
| Here's a weird thing... From the Access database on the server in
| question, I can't use the normal procedure to link to the tables in the
| remote MySQL database. But, if I use the Access database on my computer
| to establish linked tables to the remote MySQL database, and then upload
| the Access database to the problem Win 2003 server, guess what... the
| table links are retained, the linked tables are accessible, allowing
| deletes and writes etc. Doesn't make sense at all to me how this could
| happen. But I am going to try and re-write the procedures in the
| database to use Insert queries into the linked tables, rather than table
| transfers. See if that solves it.
|
| Thanks for the suggestion about updating the MySQL ODBC driver, however
| we already are using the latest GA release.
|
| --
| Steve Schapel, Microsoft Access MVP
|
|
| Dave Patrick wrote:
| > You might check for an updated mysql driver. http://dev.mysql.com/
| >

	Windows Server Forum Index -> General Discussion	All times are GMT Goto page 1, 2 Next
Page 1 of 2