Create ODBC with VB Code 1

[UncleT]

Hello everyone,

I am trying to create an odbc using VB code. I can get it to work with the following code if I leave the UserID and Password attributes out. Does anyone know what I am doing wrong. I looked everywhere on the internet for the answer and all I found is code that will create and odbc using the NT login (Trusted connection). I want an ODBC Created using the SQL Server connection with userid = "JACK" and Password = "SPRAT". I have tried UID, Login ID, etc for the attribute but none has worked. Any help would be appreciated.

'Write this code in General Declaration of form
'**************************************
'Windows API/Global Declarations for :Cr
' eating System and File DSN
'**************************************
Private Const ODBC_CONFIG_DSN = 2 ' Configure (edit) data source
Private Const ODBC_REMOVE_DSN = 3 ' Remove data source
Private Const ODBC_ADD_DSN = 4
Private Const vbAPINull As Long = 0&


Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal hwndParent As Long, _
ByVal fRequest As Long, ByVal lpszDriver As String, _
ByVal lpszAttributes As String) As Long


Private Declare Function GetActiveWindow Lib "user32.dll" () As Long


Private Declare Function SQLAllocEnv Lib "ODBC32.DLL" (phenv&) As Integer


Private Declare Function SQLAllocConnect Lib "ODBC32.DLL" (ByVal henv&, hDBC&) As Integer


Private Declare Function SQLCreateDataSource Lib "ODBCCP32.DLL" (ByVal hwndParent As Long, ByVal lpszDriver As String)


Private Declare Function SQLDriverConnect Lib "ODBC32.DLL" (ByVal hDBC As Long, ByVal hwnd As Long, _
ByVal szCSIn As String, ByVal cbCSIn As Long, ByVal szCSOut As String, _
ByVal cbCSMax As Long, cbCSOut As Long, ByVal f As Long) As Long


Private Declare Function SQLAllocStmt Lib "ODBC32.DLL" (ByVal hDBC As Long, HStmt As Long) As Long

Public Function CreateDSN(ByVal pstrServer As String, ByVal pstrDescription As String, ByVal pstrDSN As String, ByVal pstrDataBase As String) As Long
On Error Resume Next
Dim strDriver As String
Dim strAttributes As String
'set the driver to SQL Server because it
' is most common.
strDriver = "SQL Server" 'set the attributes delimited by null.
'See driver documentation for a complete
'list of supported attributes.
strAttributes = "SERVER=" & pstrServer & Chr(0)
strAttributes = strAttributes & "DESCRIPTION=" & pstrDescription & Chr(0)
strAttributes = strAttributes & "DSN=" & pstrDSN & Chr(0)
strAttributes = strAttributes & "DATABASE=" & pstrDataBase & Chr(0)
strAttributes = strAttributes & "Trusted_Connection=No" & Chr$(0)
strAttributes = strAttributes & "UID=sa" & Chr$(0)
strAttributes = strAttributes & "PWD=password " & Chr$(0)
'Calls API to create DSN
CreateDSN = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, strDriver, strAttributes)
End Function
'Write this code in General Declaration of form
'**************************************
'Windows API/Global Declarations for :Cr
' eating System and File DSN
'**************************************
Private Const ODBC_CONFIG_DSN = 2 ' Configure (edit) data source
Private Const ODBC_REMOVE_DSN = 3 ' Remove data source
Private Const ODBC_ADD_DSN = 4
Private Const vbAPINull As Long = 0&


Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal hwndParent As Long, _
ByVal fRequest As Long, ByVal lpszDriver As String, _
ByVal lpszAttributes As String) As Long


Private Declare Function GetActiveWindow Lib "user32.dll" () As Long


Private Declare Function SQLAllocEnv Lib "ODBC32.DLL" (phenv&) As Integer


Private Declare Function SQLAllocConnect Lib "ODBC32.DLL" (ByVal henv&, hDBC&) As Integer


Private Declare Function SQLCreateDataSource Lib "ODBCCP32.DLL" (ByVal hwndParent As Long, ByVal lpszDriver As String)


Private Declare Function SQLDriverConnect Lib "ODBC32.DLL" (ByVal hDBC As Long, ByVal hwnd As Long, _
ByVal szCSIn As String, ByVal cbCSIn As Long, ByVal szCSOut As String, _
ByVal cbCSMax As Long, cbCSOut As Long, ByVal f As Long) As Long


Private Declare Function SQLAllocStmt Lib "ODBC32.DLL" (ByVal hDBC As Long, HStmt As Long) As Long

Public Function CreateDSN(ByVal pstrServer As String, ByVal pstrDescription As String, ByVal pstrDSN As String, ByVal pstrDataBase As String) As Long
On Error Resume Next
Dim strDriver As String
Dim strAttributes As String
'set the driver to SQL Server because it
' is most common.
strDriver = "SQL Server" 'set the attributes delimited by null.
'See driver documentation for a complete
'list of supported attributes.
strAttributes = "SERVER=" & pstrServer & Chr(0)
strAttributes = strAttributes & "DESCRIPTION=" & pstrDescription & Chr(0)
strAttributes = strAttributes & "DSN=" & pstrDSN & Chr(0)
strAttributes = strAttributes & "DATABASE=" & pstrDataBase & Chr(0)
strAttributes = strAttributes & "Trusted_Connection=No" & Chr$(0)
strAttributes = strAttributes & "UID=sa" & Chr$(0)
strAttributes = strAttributes & "PWD=password " & Chr$(0)
'Calls API to create DSN
CreateDSN = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, strDriver, strAttributes)
End Function

 

[sglugove]

Well, UncleT, I believe that the problem is yor SQL Server. Maybe it is configured to use NT Autentication only. You can ckeck this by selecting 'Properties' in the SQL Server.

 

[rachelgeoghegan]

Hi there,

I have added this code to an existing project & it just doesn't work, I have modified the server name, database name, user name & password , & DSN name to relevant variables. I don't get any error, the code is executed but nothing at all happens - any ideas?

Thanks,

Rachel

 

[UncleT]

Rachel,

I started using the following code. I put it in a module and bring it into whatever projects I need. I am currently using it in the SETUP1.VBP to create my ODBC's at install time. With Windows 2000 policies some PC's may not be able to create an ODBC if they are not an administrator. Here is the code that I use. Look at the case statement under the g_s_Create_ODBC subroutine to determine the DriverName. You can probably use it with other database but I haven't tried. Hope this helps.

UncleT

Option Explicit

Private Const REG_SZ = 1 'Constant for a string variable type.
Private Const HKEY_LOCAL_MACHINE = &H80000002

Private Declare Function RegCreateKey Lib "advapi32.dll" Alias _
"RegCreateKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, _
phkResult As Long) As Long

Private Declare Function RegSetValueEx Lib "advapi32.dll" Alias _
"RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
ByVal Reserved As Long, ByVal dwType As Long, lpData As Any, ByVal _
cbData As Long) As Long

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


Public Sub g_s_Create_ODBC(DatasourceName As String, DatabaseName As String, _
Description As String, DriverName As String, _
LastUser As String, regional As String, server As String)

On Error Resume Next

Dim DriverPath As String

Select Case UCase(DriverName)
Case "SQL SERVER": DriverPath = "C:\WINNT\system32\sqlsrv32.dll"
Case "MICROSOFT ACCESS DRIVER (*.MDB)": DriverPath = "C:\WINNT\system32\ODBCJT32.dll"
End Select

Dim lResult As Long
Dim hKeyHandle As Long

'Specify the DSN parameters.

'Create the new DSN key.

lResult = RegCreateKey(HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\" & _
DatasourceName, hKeyHandle)

'Set the values of the new DSN key.

lResult = RegSetValueEx(hKeyHandle, "Database", 0&, REG_SZ, _
ByVal DatabaseName, Len(DatabaseName))
lResult = RegSetValueEx(hKeyHandle, "Description", 0&, REG_SZ, _
ByVal Description, Len(Description))
lResult = RegSetValueEx(hKeyHandle, "Driver", 0&, REG_SZ, _
ByVal DriverPath, Len(DriverPath))
lResult = RegSetValueEx(hKeyHandle, "LastUser", 0&, REG_SZ, _
ByVal LastUser, Len(LastUser))
lResult = RegSetValueEx(hKeyHandle, "Server", 0&, REG_SZ, _
ByVal server, Len(server))

'Close the new DSN key.

lResult = RegCloseKey(hKeyHandle)

'Open ODBC Data Sources key to list the new DSN in the ODBC Manager.
'Specify the new value.
'Close the key.

lResult = RegCreateKey(HKEY_LOCAL_MACHINE, _
"SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", hKeyHandle)
lResult = RegSetValueEx(hKeyHandle, DatasourceName, 0&, REG_SZ, _
ByVal DriverName, Len(DriverName))
lResult = RegCloseKey(hKeyHandle)

End Sub

 

[rachelgeoghegan]

Hi there UncleT,

It works perfectly, thanks a million.

I have a few queries in relation to it that you may be able to help with :

1. Is there a way of passing in a password?
2. Do you know where I can find code to delete a data source using the same method?

Thanks again for your assistance, it saved me hours of trawling!

 

[UncleT]

You can pass a password if you are opening it with ADO.

Set g_Cn_DB = New ADODB.Connection
g_Cn_DB.CursorLocation = adUseClient
g_Cn_DB.Mode = adModeReadWrite
g_Cn_DB.Provider = "SQLOLEDB.1"
g_Cn_DB.Properties("Persist Security Info&quot = False
g_Cn_DB.Properties("User ID&quot = "??"
g_Cn_DB.Properties("PASSWORD&quot = "?????"
g_Cn_DB.Properties("Initial Catalog&quot= "??"
g_Cn_DB.Properties("Data Source&quot = "??"
g_Cn_DB.Open

As far as your second question your answer may be found here.

http://www.tek-tips.com/viewthread.cfm?spid=706&newpid=706&sqid=670112

Hope this helps

 

[Glasgow]

Have you considered using rdoEngine.rdoRegisterDataSource? A lot less code but not sure if it's what you want to achieve. Code something like:

Dim strAttribs As String
strAttribs = "Description=" _
& App.EXEName & " link to " & Mnemonic & " database" _
& Chr$(13) & "OemToAnsi=No" _
& Chr$(13) & "SERVER=MySqlServer" _
& Chr$(13) & "Network=DBNMPNTW" _
& Chr$(13) & "Trusted_Connection=Yes" _
& Chr$(13) & "Database=MyDb"
rdoEngine.rdoRegisterDataSource "MyDSN", _
"SQL Server", True, strAttribs

You need a reference to Microsoft Remote Data Object in your project.

 

[UncleT]

This may also work for you second question.

DECLARE Integer SQLConfigDataSource IN odbccp32.dll Integer, ;
Integer, String, String
DriverName="Microsoft Visual FoxPro Driver"
IsRemove =SQLConfigDataSource(1,3,DriverName, "DSN=rmtest" + CHR(0)+;
"Description=david test"+CHR(0)+;
"SourceDB=C:\TEMP\data1.dbc"+CHR(0)+;
"SourceType=DBC&quot
IF IsRemove>0
WAIT WINDOW "Deleted"
ELSE
WAIT WINDOW "Delete failed"

 

[rachelgeoghegan]

Thanks for all this assistance.

To answer Glasgows comment - I'm using ADO so I wouldn't imagine the RDO register data source method would be of much use for this particular project but is v.handy thing to know! Thanks.

Uncle T - whats the story with that code VB6 doesn't seem to like it - I'm just playing around with it - what language is it?

 

[Glasgow]

Not 100% sure if .rdoRegisterDataSource will support

strAttributes = strAttributes & "Trusted_Connection=No" & Chr$(13)
strAttributes = strAttributes & "UID=sa" & Chr$(13)
strAttributes = strAttributes & "PWD=password " & Chr$(13)

but worth a shot perhaps!

 

[UncleT]

If you are talking about the ADO code it should work. I forgot to add the following pieces.

Global g_Cn_DB As ADODB.Connection

You must also reference
Microsoft ActiveX Data Objects 2.6 library

if you are talking about the ODBC Delete. I pulled it off of another website. I have never used it.

Sorry

 

[baugie]

Hope it isn't too late to pick up on this thread...

Uncle T,
I had also used the Microsoft source code for adding a DSN. However, when I run it on my XP maching the registry settings are added but the DSN doesn't show up in the ODBC Data Source Admin. screen and are not accessible from other programs. However, to increase my frstration I ran the same code on a W2K pc and it worked fine.

The topper is that when I manually try to add the DSN, it says the name already exists, do you want to overwrite. I say yes, successfully complete the DSN and it still does not show up in the list. When I make a completely different DSN, it also does not show up in the list. (this is all on my XP pc) So now that I've hosed my computer, any ideas on what the problem may be??

thanks in advance for the help

baugie

 

[Glasgow]

Rachel - probably too late in coming back on this - only because I saw the thread again thanks to Baugie's reactivation.

The fact that you are using ADO throughout the rest of your code (as I do) does not stop you using RdoRegisterDataSource just to create the DSN but it does need a reference to the Microsoft Remote Data Object library in the project.

 

[UncleT]

baugie

No ideas. My company is using this code on all Win2k machines. We do not have any XP machines so I can't really test it.

Sorry

UncleT

 

[baugie]

Thanks for responding. I found a non-official fix for the problem. Within the registry located at

HKLM\Software\ODBC\ODBC.INI\ODBC Data Sources

the first key is a Default key. I deleted the key, which is not allowed but is does reset something because it allowed my ODBC Manager screen to refresh and all of the DSNs appeared. Very strange indeed.

Thanks

baugie

 

[UncleT]

Thank You,

That is good to know for future reference.

UncleT