Old apps SQLConfigDataSource failing on Vista

[LittleSmudge]

I'm an Access database developer and a client has been running my apps on Windoz 2k - 2k3 for years but they can't get 2k3 licences anymore so we need to get them running on Vista

Standard structure is Access front ends all resident on user's local machines using ODBC to MySQL BackEnd on the server.

There is a DbUpdate app ( an Access database ) which sets up new machines automaticlly. It first checks that ODBC DSN connection exists and if it does not runs

Private Declare Function SQLConfigDataSource Lib "odbccp32.dll" _
                        (ByVal hwndParent As Long, _
                        ByVal fRequest As Integer, _
                        ByVal lpszDriver As String, _
                        ByVal lpszAttributes As String) As Long

Const ODBC_ADD_SYS_DSN = 4


Public Function CheckDSN

lngResult = SQLConfigDataSource(0, _
                     ODBC_ADD_SYS_DSN, _
                     "MySQL ODBC 3.51 Driver", _
                     "DSN=" & DSNName & Chr(0) & _
                     "Server=" & FGServerName & Chr(0) & _
                     "Database=" & DatabaseName & Chr(0) & _
                     "UseProcForPrepare=Yes" & Chr(0) & _
                     "Description=" & Description & Chr(0) & Chr(0))

However in Vista the SQLConfigDataSource fails to create the DSN

Has anyone else hit this problem ?
Any ideas for a fix ?



G LS

 

[cheerio]

I have no knowledge of Vista BUT this sounds like a user rights problem. If I remeber correctly system DSNs are stored in the registry so the user needs rights to change the registry. In the absence of other suggestions I would test logged on as an administrator.

 

[LittleSmudge]

Thanks cheerio,

We're coming to the same conclusion from other directions too.



G LS

 

[jrbarnett]

To clarify what cheerio says:

System DSN's can only be added/edited/deleted by accounts with administrator permissions on the local machine. They are stored in (under XP Pro at least, haven't got a vista machine to check this on):
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI

User DSNs are stored in:
HKEY_CURRENT_USER\Software\ODBC\ODBC.INI

Possibilities:
- Rewrite as a user DSN. Problem here is that if you have roaming profiles in place, they won't run properly unless your MyODBC drivers are installed on each PC.
- Roaming profiles are OS specific. If you install on an XP machine but then move to a vista or 2K box, your ODBC settings need to be applied to that profile as well to work properly.
- Use a file DSN stored on a server file share and connect through that. You still need to install the MyODBC, but no messing with the Registry is needed. Trouble is, not all apps can work properly with a file DSN.

John