Application ODBC check and Build help

Application ODBC check and Build help

Post by Jeff » Sat, 22 Nov 2003 06:42:35


i,

I have a microsoft access front end database that connects
to a SQL 2000 set of tables. This is done with an ODBC
driver using SQL authentication. Since this front end
database is used by several users I an trying to code in
the application to check and see if a DSN exsists. If it
doesn't then it will build the ODBC DSN. I have had some
success. I can build all function except the sql
authentication. The system wants to default to the NT
user. I have included all the code I have in the
application on the check and build. I believe I am just
missing the User ID and Password description fields and
their values. Any help anyone can give me will be great.


Const JDS_DSN_name = "CAOSQL1"
Const JDS_Server_name = "GAALPINVT08" ' Raw IP address
is used to avoid NT _

Domain name resolution probs.

Private Declare Function RegEnumKeyEx Lib "advapi32.dll" _
Alias "RegEnumKeyExA" _
(ByVal hKey As Long, _
ByVal dwIndex As Long, _
ByVal lpName As String, _
lpcbName As Long, _
ByVal lpReserved As Long, _
ByVal lpClass As String, _
lpcbClass As Long, _
ByVal lpftLastWriteTime As String) As Long

Private Declare Function RegOpenKeyEx Lib "advapi32.dll" _
Alias "RegOpenKeyExA" _
(ByVal hKey As Long, _
ByVal lpSubKey As String, _
ByVal ulOptions As Long, _
ByVal samDesired As Long, phkResult As Long) As Long

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

Private Declare Function RegCloseKey Lib "advapi32.dll" _
(ByVal hKey As Long) As Long

Const HKEY_LOCAL_MACHINE = &H80000002

Const ERROR_SUCCESS = 0&
Const SYNCHRONIZE = &H100000
Const STANDARD_RIGHTS_READ = &H20000
Const STANDARD_RIGHTS_WRITE = &H20000
Const STANDARD_RIGHTS_EXECUTE = &H20000
Const STANDARD_RIGHTS_REQUIRED = &HF0000
Const STANDARD_RIGHTS_ALL = &H1F0000
Const KEY_QUERY_VALUE = &H1
Const KEY_SET_VALUE = &H2
Const KEY_CREATE_SUB_KEY = &H4
Const KEY_ENUMERATE_SUB_KEYS = &H8
Const KEY_NOTIFY = &H10
Const KEY_CREATE_LINK = &H20
Const KEY_READ = ((STANDARD_RIGHTS_READ Or _
KEY_QUERY_VALUE Or _
KEY_ENUMERATE_SUB_KEYS Or _
KEY_NOTIFY) And _
(Not SYNCHRONIZE))

Const REG_DWORD = 4
Const REG_BINARY = 3
Const REG_SZ = 1

Const ODBC_ADD_SYS_DSN = 4


Function Check_SDSN()

' Look for our System Data Source Name. If we
find it, then great!
' If not, then let's create one on the fly.

Dim lngKeyHandle As Long
Dim lngResult As Long
Dim lngCurIdx As Long
Dim strValue As String
Dim classValue As String
Dim timeValue As String
Dim lngValueLen As Long
Dim classlngValueLen As Long
Dim lngData As Long
Dim lngDataLen As Long
Dim strResult As String
Dim DSNfound As Long
Dim syscmdresult As Long

syscmdresult = SysCmd(acSysCmdSetStatus, "Looking
for System DSN " & JDS_DSN_name & " ...")

' Let's open the registry