The easiest way to connect to an SQL Server database with SQL functions is to employ Microsoft’s ActiveX Data Objects. The connection will be part of a Visual Basic application you are developing. It is added to the module you write to select, update, delete or insert into your database table.
- Skill level:
Other People Are Reading
Start a new project in Visual Studio by clicking on the File menu and selecting “New Project.” Click on the “Windows Application” icon in the New Projects Templates panel. Enter a name for the project in the “Name” field in the window. Click on the “OK” button to create the project files. The new project will contain a blank form. Create the form to access fields in your database.
Add in the ADO function to your project. Click on the Project menu on the tool bar of Visual Studio and then select “References” from the drop-down menu. Scroll down through the “Available Reference” list and click in the check box for “ActiveX Data Objects 2.8 Library” entry. Click the “OK” button to save this setting.
Click on the “Modules” node in the “Project” code tree in the top left hand window of the Studio. Your SQL commands will go into a sub procedure here, but you need to declare an object of the type “ADODB.” It is common practice to call this instance of the object “con.” The declaration takes the form: Dim con As New ADODB.Connection
Instantiate the con object with the values needed to connect to the database. This will enable you to connect to your database and start issuing SQL commands in your VB program. The following is a typical set of values as recommended by Microsoft on the Microsoft Developers Network website: con.ConnectionString = "Provider=SQLNCLI11;" & "Server=(local);" & "Database=AdventureWorks;" _ & "Integrated Security=SSPI;" & "DataTypeCompatibility=80;" & "MARS Connection=True;" In this example, the database to be opened is called “AdventureWorks.” Replace this name with the name of your database. Also, the “(local)” value for the server name can be replaced by the location and file name of your database file on your computer. The “MARS” line is optional. It enables access to the “multiple active result sets” feature of SQL Server.
Connect to the SQL Server database with the following line of code: Con.Open You will now be able to write SQL statements in your VB program to interact with tables in the SQL database.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for