Solved

Dsn creation by VB Codeing for MS ACCESS

Posted on 2003-11-03
10
263 Views
Last Modified: 2010-04-17
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

                       Mkb
0
Comment
Question by:mkbarsiwal
10 Comments
 
LVL 53

Accepted Solution

by:
Dhaest earned 125 total points
ID: 9669940
0
 
LVL 9

Assisted Solution

by:bhagyesht
bhagyesht earned 125 total points
ID: 9676552
this is for SQL just change the connection string for access


Option Explicit
'Declarations
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

'PURPOSE: 'CREATES A SYSTEM DSN FOR AN SQL SERVER DATABASE
'PARAMETERS: 'DSNName = DSN Name
             '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

'PURPOSE: 'CREATES A SYSTEM DSN FOR AN ACCESS DATABASE
'PARAMETERS: 'DSNName = DSN Name
             'DatabaseFullPath = Full Path to .mdb file
'RETURNS: True if successful, false otherwise
'EXAMPLE: CreateAccessDSN "MyDSN", "C:\MyDb.mdb"

    Dim sAttributes As String
   
    'TEST TO SEE IF FILE EXISTS: YOU CAN REMOVE IF YOU
    'DON'T WANT IT
    If Dir(DatabaseFullPath) = "" Then Exit Function
   
sAttributes = "DSN=" & DSNName & Chr(0)
sAttributes = sAttributes & "DBQ=" & DatabaseFullPath & Chr(0)
CreateAccessDSN = CreateDSN("Microsoft Access Driver (*.mdb)", _
   sAttributes)

End Function

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

'PURPOSE: CREATES A SYSTEM DSN
'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
0
 

Expert Comment

by:asifk
ID: 9693192
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:


objConn.Open

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 9

Expert Comment

by:bhagyesht
ID: 9699285
asifk: Sorry by DNS creation by VB codeing is the question what you wrote is manual stuff.
0
 

Author Comment

by:mkbarsiwal
ID: 9700009
Thanks friends for cooperation for my query.
0
 
LVL 9

Expert Comment

by:bhagyesht
ID: 9700026
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.
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 10319708
My recommandation: split points between bhagyesht and dhaest
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now