Creating a DSN from within vb

[rajeshkhanna_in]

Hi all
I have created an application which automates the Preparation of Question Papers for Various Exams. This software uses a DSN connection to a ACCESS database. While I was developing software I created this connection manually but while I distribute my application this connection will have to be created on every machine. Is there a way in which this can be automated via vb 6.0?

Any help will be appriciated..

Thanks

Rajesh Khanna

[K.Vanlalliana]

Hi,

Make your connection using DAO so that your problem will be solved. For writing connection, do like this:

'Declare the connection
Public ConnectData As New DODB.Connection

'For opening the Database
Set ConnectData = New ADODB.Connection
ConnectData.Provider = "Microsoft.Jet.OLEDB.4.0; Data Source =" & App.Path & "\System\Record\Data\StatisticalRecord.txt; Jet OLEDB:Database Password=vlednst;"
'ConnectData.Provider = "Microsoft.Jet.OLEDB.4.0; Data Source = " & App.Path & "\databaseName.mdb;" ConnectData.Open

It is best to write this connection string in the module so that whenever you need new connection, you can easily call it by its Sub-Name.

If you are not clear from this,don't hesitate to share your problem

[K.Vanlalliana]

Hi,

If you are makeing connection using DSN/ODBC, you will have to set it manually for every install programme.

Make your connection using DAO so that your problem will be solved. For writing connection, do like this:

'Declare the connection
Public ConnectData As New DODB.Connection

'For opening the Database
Set ConnectData = New ADODB.Connection

ConnectData.Provider = "Microsoft.Jet.OLEDB.4.0; Data Source = " & App.Path & "\databaseName.mdb;"

ConnectData.Open

It is best to write this connection string in the module so that whenever you need new connection, you can easily call it by its Sub-Name.

If you are not clear from this,don't hesitate to share your problem

To write this code in your module.form for connecting to your database, you may need to Select "Microsoft ActiveX data Object 2.0" or higher number. otherwise, it will give connection error.

[hkdani]

Is there a way in which this can be automated via vb 6.0?

I would look at your situation and determine where the variables will occur in making your data source. If the only thing that is changing is the address of your data source, then you should have a menu item or some other means of allowing the user to set the address of the data source. You could store that information in the registry using the SaveSetting function: e.g. SaveSetting "MyProgram", "Data", "DataAddress", strSetting

You should use some kind of error checking to ensure that the user entered a properly formed string.

Private Sub RegisterDataSource()
Dim en As rdoEnvironment
Dim cnTest As rdoConnection
Dim strAttribs As String
Dim strAddress As String

' Build keywords string.
strAttribs = "Description=" _
        & "SQL Server on server SEQUEL" _
  & Chr$(13) & "OemToAnsi=No" _
  & Chr$(13) & "SERVER=SEQUEL" _
  & Chr$(13) & "Network=DBNMPNTW" _
  & Chr$(13) & "Database=WorkDB" 
  
strAddress = GetSetting("MyProgram", "Data", "DataAddress", "NotEntered")
if strAddress = "NotEntered" then
     msgbox "Please enter Data Address Information before making connection", vbCritical, "Data Address needed"
Else
     strAttribs = strAttribs & Chr$(13) & strAddress
End if
' Create new registered DSN.
rdoEngine.rdoRegisterDataSource "Example", _
         "SQL Server", True, strAttribs
' Open the database.
Set en = rdoEngine.rdoEnvironments(0)
Set cnTest = en.OpenConnection( _
  dsname:="Example", _
  Prompt:=rdDriverNoPrompt, _
  Connect:="UID=;PWD=;")
  

End Sub

[rajeshkhanna_in]

Hi again
I appreciate your help. But the problem is that I have not programatically connected to the database instead i added the ADO component and then kept placing them on the forms as and when i required them. Now i will have to change everything if I wanted to change the source from ODBC/DSN to RDO. Is there a shortcut ?

Please reply

Rajesh Khanna