Go Back   Access World Forums > Apps and Windows > VB.NET

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-04-2019, 08:55 PM   #1
essaytee
Need a good one-liner.
 
essaytee's Avatar
 
Join Date: Oct 2008
Location: Melbourne, Australia
Posts: 279
Thanks: 5
Thanked 47 Times in 46 Posts
essaytee is on a distinguished road
Can not connect to SQLExpress in VB but can from SSMS

I've taken the plunge, now trialling SQLExpress along with VB. I'm following an online tutorial but have got to a point where I can't connect to an MSSQL database.

I've installed SQLExpress 2017 and the SSMS tools program. They were both installed to their default locations on my C: drive.

From SSMS I created a database, "Test.mdf", created one table, "Contacts" and added some dummy data. The "Test.mdf" is located here:
Code:
"C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA"
The Server Name = "MyComputerName\sqlexpress"

The following is the connection string:

Code:
MyCn.ConnectionString = "Data Source=MyComputerName\SQLEXPRESS; AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\Test.mdf; " &
            “User Instance=True;Integrated Security=SSPI”
that I'm using, as suggested by the tutorial, but when the code gets to MyCn.Open() it errors out as follows:

Code:
System.Data.SqlClient.SqlException:
'Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\Test.mdf". 
Operating system error 5: "5(Access is denied.)".
An attempt to attach an auto-named database for file
C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\Test.mdf failed.
A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.'
My user account has Administrator Rights. Any assistance would be appreciated.

Steve.

ps. For completeness, the following is the entire code snippet:

Code:
Public Class Form1
    Private MyDatAdp As New SqlDataAdapter
    Private MyCmdBld As New SqlCommandBuilder
    Private MyDataTbl As New DataTable
    Private MyCn As New SqlConnection
    Private MyRowPosition As Integer = 0

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        MyCn.ConnectionString = "Data Source=MyComputerName\SQLEXPRESS; AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\Test.mdf; " &
            “User Instance=True;Integrated Security=SSPI”

        MyCn.Open()    ' errors out here

        MyDatAdp = New SqlDataAdapter("Select* from Contacts", MyCn)
        MyCmdBld = New SqlCommandBuilder(MyDatAdp)
        MyDatAdp.Fill(MyDataTbl)

        Dim MyDataRow As DataRow = MyDataTbl.Rows(0)
        Dim strName As String
        Dim strState As String
        strName = MyDataRow("ContactName")
        strState = MyDataRow("State")
        TxtName.Text = strName.ToString
        TxtState.Text = strState.ToString()
        Me.showRecords()
    End Sub

    Private Sub showRecords()
        If MyDataTbl.Rows.Count = 0 Then
            txtName.Text = ""
            txtState.Text = ""
            Exit Sub
        End If
        txtName.Text = MyDataTbl.Rows(MyRowPosition)("ContactName").ToString()
        TxtState.Text = MyDataTbl.Rows(MyRowPosition)("State").ToString()
    End Sub
   
End Class

__________________
Steve.

I know UNIX, PASCAL, C, FORTRAN, COBOL, and nineteen other high-tech words.

Last edited by essaytee; 01-05-2019 at 07:34 PM. Reason: correct link
essaytee is offline   Reply With Quote
Old 01-04-2019, 11:46 PM   #2
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,340
Thanks: 83
Thanked 1,420 Times in 1,340 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Can not connect to SQLExpress in VB but can from SSMS

The mdf files store the data but the interaction is through a named database in the engine. Don't attach an mdf file.

Normally the string would use the "database" attribute with the name of the database.

https://www.connectionstrings.com/mi...r-odbc-driver/
Galaxiom is offline   Reply With Quote
The Following User Says Thank You to Galaxiom For This Useful Post:
essaytee (01-05-2019)
Old 01-05-2019, 02:54 AM   #3
essaytee
Need a good one-liner.
 
essaytee's Avatar
 
Join Date: Oct 2008
Location: Melbourne, Australia
Posts: 279
Thanks: 5
Thanked 47 Times in 46 Posts
essaytee is on a distinguished road
Re: Can not connect to SQLExpress in VB but can from SSMS

Thanks Galaxiom for the link and advice, all sorted now. After a few failed attempts it finally worked. The following connection string did the trick:

Code:
"Server=.\SQLExpress; AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\Test.mdf; Database=Test; Trusted_Connection=True;"

__________________
Steve.

I know UNIX, PASCAL, C, FORTRAN, COBOL, and nineteen other high-tech words.
essaytee is offline   Reply With Quote
Old 01-05-2019, 02:59 AM   #4
essaytee
Need a good one-liner.
 
essaytee's Avatar
 
Join Date: Oct 2008
Location: Melbourne, Australia
Posts: 279
Thanks: 5
Thanked 47 Times in 46 Posts
essaytee is on a distinguished road
Re: Can not connect to SQLExpress in VB but can from SSMS

And finally, after reading your advice once again, I removed the AttachDbFilename reference, and a connection is made.

Connection string is now:

Code:
"Server=.\SQLExpress; Database=Test; Trusted_Connection=True;"

__________________
Steve.

I know UNIX, PASCAL, C, FORTRAN, COBOL, and nineteen other high-tech words.
essaytee is offline   Reply With Quote
Reply

Tags
connection , sqlserver

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query Access table in SSMS vqcheese SQL Server 1 11-07-2013 04:02 PM
Access F/E + SQLExpress B/E <> MSExchange2003 New2VB SQL Server 2 08-10-2010 12:18 AM
Paging an SQLExpress attached database malc_fowler Forms 1 06-06-2008 03:58 AM
SQLEXPRESS and ADP pozzo General 1 05-28-2005 01:45 PM
SQLExpress and ADP pozzo General 1 05-18-2005 04:54 AM




All times are GMT -8. The time now is 04:45 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World