473,888 Members | 2,199 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to Create a Data Link File

ADezii
8,834 Recognized Expert Expert
Rather than using CurrentProject. Connection or entering your own Connection information, ADO supports storing Connection information in an external file called a Data Link File (which normally has a *.UDL extension). Data Link Files provide two very important capabilities:
  1. They implement a graphical interface for constructing what can be complex and confusing OLE DB Connection Strings.
  2. They offer a way to allow Users to edit Connection information, for example, by entering their own User Name and Password, without the necessity of writing code to capture this information.
-----------------------------------------------------------------------------------------------------------------------

The process of creating a new Data Link File is simple and straightforward :
  1. Right Click in Windows Explorer and choose New ==> Text Document. Doing so will create a New Text Document (*.TXT) in the current Folder.
  2. Rename the file to any name you like, changing the extension to .UDL. Windows will warn you that changing the file name extension may make the file unusable; respond Yes to this warning.
  3. Double-Click the file to edit the Data Link properties.
  4. Select the Provider tab and choose the appropriate Provider.
  5. Select the Connection Tab and fill in the Database Name and Log in information.
  6. Click the Test Connection button to verify that the information entered is correct, and then click OK to save the file.
-----------------------------------------------------------------------------------------------------------------------

Once you've created a Data Link File, you needn't supply any information when opening the Connection in code other than the location of the *.UDL file. This process makes it simple for you to manage changing Data Sources without having to modify any code. When you want to open a Connection based on a UDL file, you simply specify the *.UDL file name as the ConnectionStrin g property, or specify this information when you call the Open Method.
For the purpose of this Tip, I'll create a Data Link File (SQLServer.udl) that will establish a Connection to the Northwind Database residing in a Local installation of SQL Server. A Connection will be opened via the Northwind.udl Data Link File, a Recordset will be created based on the Employees Table, and the First and Last names of Northwind Employees will be printed to the Immediate Window.

-----------------------------------------------------------------------------------------------------------------------

Relevant information regarding the creation of the Data Link File is as follows:
  1. Provider Tab
    1. Provider: Microsoft OLE DB for SQL Server
  2. Connection Tab
    1. Server Name: (local)
    2. Select a specific User Name and Password Log on option
    3. User Name: sa
    4. Password: none
    5. Select Database on Server: Northwind
-----------------------------------------------------------------------------------------------------------------------

Now, the code segment that ties it all together. Pay particular attention to Line #6:
Expand|Select|Wrap|Line Numbers
  1. Dim cnnSQL As ADODB.Connection
  2. Dim rstEmployees As New ADODB.Recordset
  3.  
  4. Set cnnSQL = New ADODB.Connection
  5.  
  6. cnnSQL.Open "File Name=" & "C:\Test\SQLServer.udl"
  7. Debug.Print cnnSQL.ConnectionString
  8.  
  9. rstEmployees.Open "Employees", cnnSQL, adOpenKeyset, adLockOptimistic
  10.  
  11. Do While Not rstEmployees.EOF
  12.   Debug.Print rstEmployees![FirstName] & " " & rstEmployees![LastName]
  13.   rstEmployees.MoveNext
  14. Loop
  15.  
  16. rstEmployees.Close
  17. cnnSQL.Close
  18.  
  19. Set rstEmployees = Nothing
  20. Set cnnSQL = Nothing
-----------------------------------------------------------------------------------------------------------------------

Connection String generated from code line #7:
Expand|Select|Wrap|Line Numbers
  1. Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;
  2. Initial Catalog=Northwind;Data Source=(local);Use Procedure for Prepare=1;
  3. Auto Translate=True;Packet Size=4096;Workstation ID=DEZII;
  4. Use Encryption for Data=False;Tag with column collation when possible=False
Dec 3 '07 #1
0 14311

Sign in to post your reply or Sign up for a free account.

Similar topics

9
11252
by: Lauren Quantrell | last post by:
Is there a way to create a text file (such as a Windows Notepad file) by using a trigger on a table? What I want to do is to send a row of information to a table where the table: tblFileData has only one column: txtOutput I want to use the DB front end (MS Access) to send the text string to the SQL backend, then have the SQL Server create a file to a path, such as F:/myfiledate.txt that holds the text in txtOutput, then the trigger...
3
7005
by: G rumpy O ld D uffer | last post by:
This is probably a 'Low-Level' question to all the ACCESS experts but I've only been using ACCESS for a couple of weeks. I've been given 30+ (and counting) separate 'Weekly' Databases which all have a Table, in exactly the same Field format, in each of these 'Weekly' DataBases. I want to set-up a new 'Master' Database, so that I can write one Query to search all the 'Weekly' Databases and produce one new Table in the 'Master' Database...
18
3600
by: Al | last post by:
I'm still trying to do this but it never worked! In a .cpp file, I write the code, and at the beginning, I write: #ifndef MYLIST_H #define MYLIST_H ....to end: #endif What's wrong with it for creating a header file when compiling? Do I need to write in the block the #include (s)?
11
3450
by: mesut demir | last post by:
Hi All, When I create fields (in files) I need assign a data type like char, varchar, money etc. I have some questions about the data types when you create fields in a file. What is the difference between data type 'CHAR' and 'TEXT'? When do you use 'VAR' in your datatype word? e.g. VARCHAR ?
6
3701
by: windandwaves | last post by:
Hi Folk Some of my clients asked me to create "fancy emails" for them (aka html formatted emails). I know how to make a nice html document, but I had trouble creating a simple way to provide the document to my clients so that they could use it to. I know most of them use Outlook XP or Outlook 2003, so what I created was a page that creates a Visual Basic script that, when saved to the desktop and
5
5814
by: nephish | last post by:
Hello there, i have an app that i need to be able to publish a link to download a csv file. i know that i can use php to make the file, but how to i link to it through php. like if i have an html file that has a link in it like this <a href="somedata.csv">here be data</a> or would i make that a .php file that would generate a csv ?
3
1951
by: shapper | last post by:
Hello, I need to add a XML file from an Access database. Could someone tell me what would be the best approach? Thanks, Miguel
4
13014
by: Billy Barth | last post by:
I would like to create a RSS feed from my Access Database. I have an Access table for news which has the article title, date, and story in it. What I would like to do is pull the data from there into an xml file to create the RSS feed, so when users upload a news story it automatically updates in the xml file. Can this be done with asp? and where should I look for more information. Thanks, This is a great group. -- Posted via a free...
15
7851
by: Killer42 | last post by:
Hi all. Ok, I'm using VB6 but I think the answer to this (if there is one) is more likely to be found in the Access forum. I have a situation where I've got tens of millions of records, spread over a bunch of separate databases. I don't have a choice about this, as the data is simply too large for a single MDB. As yet, I'm not prepared to go to SQL Server. Possibly in the not-too-distant future, but we'll see. In the meantime, I have this...
0
9957
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9799
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10772
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9593
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7988
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5810
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4635
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4239
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3245
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.