Dsn creation by VB Codeing for MS ACCESS

Hello sir,

          I'll be very glad if you please tell me how to create 'System Dsn' for the MSACCESS
& how to connect it with database.

       Thank you

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
this is for SQL just change the connection string for access

Option Explicit
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 Const ODBC_ADD_SYS_DSN = 4

Public Function CreateSQLServerDSN(DSNName As String, _
   ServerName As String, Database As String) As Boolean

             'ServerName = Name of Server
             'Database = Database to Use
'RETURNS: True if successful, false otherwise
'EXAMPLE: CreateSQLServerDSN "MyDSN", "MyServer", "MyDatabase"

Dim sAttributes As String

sAttributes = "DSN=" & DSNName & Chr(0)
sAttributes = sAttributes & "Server=" & ServerName & Chr(0)
sAttributes = sAttributes & "Database=" & Database & Chr(0)
CreateSQLServerDSN = CreateDSN("SQL Server", sAttributes)

End Function

Public Function CreateAccessDSN(DSNName As String, _
  DatabaseFullPath As String) As Boolean

             'DatabaseFullPath = Full Path to .mdb file
'RETURNS: True if successful, false otherwise
'EXAMPLE: CreateAccessDSN "MyDSN", "C:\MyDb.mdb"

    Dim sAttributes As String
    If Dir(DatabaseFullPath) = "" Then Exit Function
sAttributes = "DSN=" & DSNName & Chr(0)
sAttributes = sAttributes & "DBQ=" & DatabaseFullPath & Chr(0)
CreateAccessDSN = CreateDSN("Microsoft Access Driver (*.mdb)", _

End Function

Public Function CreateDSN(Driver As String, Attributes As _
  String) As Boolean

'PARAMETERS: 'Driver = DriverName
'ATTRIBUTES: 'Attributes; varies as a function
             'of the Driver
'EXAMPLE: Refer to Code Above

    CreateDSN = SQLConfigDataSource(0&, ODBC_ADD_SYS_DSN, _
      Driver, Attributes)
End Function
You can set up a DSN for an existing MS Access database file as discussed here.

Using Windows Control Panel to setup DSN
Here is how you can setup a new MS Access DSN.

Step 1.1
Run Start->Settings->Control Panel from Windows start menu.

Step 1.2
Click on Administrative Tools icon from the Control Panel window.

Step 1.3
Next click on Data Sources (ODBC) icon from the Control Panel window.

Step 1.4
From the ODBC Data Source Administrator screen select System DSN tab as shown below.

Click on the Add button to add a new system DSN.

Step 1.5
Select the Microsoft Access Driver and click on Finish button.

Click on the Add button to add a new system DSN.

Step 1.6
Enter the Data Source Name. This is the DSN. Optionally, you can enter a description.

Click on the Select button to connect the DSN to your MS Access file. For our example, we will connect to leads.mdb which is stored in C:\data directory.

Find your MS Access file and click on the OK button.

Step 1.7
Enter the Data Source Name. This is the DSN. Optionally, you can enter a description.

Click on the Select button to connect the DSN to your MS Access file. For our example, we will connect to leads.mdb which is stored in C:\data directory.

Find your MS Access file and click on the OK button.

Once you have returned to the previous dialog window, click on the OK button to add the DSN in the system DSN list.

You should see your new DSN in the system DSN list. Here our example DSN (MyLeads) appear in the system DSN list as shown below.

Step 1.7
Click on the OK button to close ODBC Data Source Administrator window. Finally, close the Control Panel as well.

How to coonect ********************

Create an object to connect to the database

Provide that object with information regarding the location and type of our database

Command that object to open a connection with the database

To do the first step, we will use one of ADO's objects, the Connection Object. To create an instance of the connection object in ASP, we need only issue the following commands:

Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")

This will fulfill step one of three: creating an object to connect to the database. Now we must provide this object with the information to connect to the database. There are two ways to provide this information. The first way is through the use of a System DSN. A System DSN is a file containing information about a particular database. This information includes the physical location of the database on the computer, what type of database it is (SQL, Access, etc.), and other pertinent information. (DSN stands for Data Source Name, another TLA)

You can create a System DSN in Windows by going to Start/Settings/ Control Panel/ODBC. There is a System DSN tab. From there you can Add a System DSN of your choice (Oracle, Informix, SQL, Access, etc.). There is a really great tutorial on how to do this at ActiveServerPages.com. I'd highly recommend you check this out if you've never created a System DSN before.

Once you have a System DSN, you need to let the connection object (objConn) know that this is the database you want to connect to. To do this, we but need add the following line:

objConn.ConnectionString = "DSN=SystemDSN Name"
So whatever you named the System DSN you created, put it after the DSN= in the connection string.

I mentioned that there were two ways to get the database information to the connection object. One way is to use a System DSN; the other way is to use what is called a DSN-less connection. This approach uglies up your connection string a bit, because you need to supply all of the important information in the connection string, since there isn't a DSN which holds that information. Access databases are the ones that typically use DSN-less connections. Here is an example of a DSN-less connection string.

objConn.ConnectionString = "DBQ=C:\WebShare\MyDatabase.mdb;DRIVER={MS Access (*.mdb)}"
DBQ tells the connection object the physical path to the database. DRIVER tells the connection object what type of database we're using; in this case, an Access database. There is a good tutorial on AcitveServerPages.com on how to connect to a database using a DSN-less connection.

OK, so now we've completed steps one and two: we've created an instance of a connection object, and provided it the information needed to connect. Now we just need to tell the connection object to open a connection with the database. This is done with the following method:


Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

asifk: Sorry by DNS creation by VB codeing is the question what you wrote is manual stuff.
mkbarsiwalAuthor Commented:
Thanks friends for cooperation for my query.
mkbarsiwal: so whats the status? have you recieved the solution? if yes Please accept the answer by clicking on the accept button and close the question. In case you still have any doubts in relation with the question please post the same.
My recommandation: split points between bhagyesht and dhaest
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.