sql connection string

Question

I'm trying to write information to a db.

I'm using VB 2010 Express and writing to a db on a shared server.

I've tried several connection strings, but I keep on getting the same exception.  SQL Network Interfaces, error 26 - Error Locating Server/Instance Specified.  Verify instance name is correct and that SQL Server is configured to allow remote connections.

"Server= .\SQLExpress; AttachDbFilename=K:Path Filename.accdb; Database=Some database name; Trusted_Connection=Yes;"

 

Answer 1

Server=.\SQLExpress is looking for a local default instance on your machine. You need to fully qualify the location. If it's a local default instance on that machine, just use the IP address or machine name as the server

Adam

---

Ctrl+Z

Answer 2

Hello,

Why are you using AttachDbFileName field ? Usually , it is because you are using the user instance feature .If true you have forgotten

User Instance = true;

See this link : http://www.connectionstrings.com/Forum/sql-server/host-conection-string

i suggest you to have a look at this link :

http://msdn.microsoft.com/en-US/library/bb264564(v=SQL.90).aspx

and have a special look at the part Common issues ( about the write/read permissions for the files and the drive )

Dont'hesitate to post again for more help or explanations

Are you sure that this database has been already attach to your .\SQLEXPRESS instance ?

Have a nice day

PS : K is a shared or network drive ? I ask this question because of the part Database files and filegroups in the Remarks of

http://msdn.microsoft.com/en-us/library/ms176061(v=SQL.100).aspx

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 3

Server=.\SQLExpress is looking for a local default instance on your machine. You need to fully qualify the location. If it's a local default instance on that machine, just use the IP address or machine name as the server

Adam

---

Ctrl+Z

Ok, if I need to do this how do I word that in my connection String.   Mind you I have looked all over the internet tried several different connection strings.  Nothing is working.

I can read from the db, I just can't write to it.  I'm using data in tables to populate comboboxes and cross check against user entered forms.

Answer 4

Hello,

Why are you using AttachDbFileName field ? Usually , it is because you are using the user instance feature .If true you have forgotten

User Instance = true;

See this link : http://www.connectionstrings.com/Forum/sql-server/host-conection-string

i suggest you to have a look at this link :

http://msdn.microsoft.com/en-US/library/bb264564(v=SQL.90).aspx

and have a special look at the part Common issues ( about the write/read permissions for the files and the drive )

Dont'hesitate to post again for more help or explanations

Are you sure that this database has been already attach to your .\SQLEXPRESS instance ?

Have a nice day

PS : K is a shared or network drive ? I ask this question because of the part Database files and filegroups in the Remarks of

http://msdn.microsoft.com/en-us/library/ms176061(v=SQL.100).aspx

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

K:  is a shared drive.  I've tried several strings in several manners.  I'm not sure if it is attached.  I've looked for similar examples in code, but haven't found any.  I work best if I have at least a similar example to work from.  If I'm missing a statement somewhere, then I'm lost as to where it is missing.

I do have the db in my Data Connections if that is what you are referring to.

Answer 5

Hello,

Please, could you have a look at this link ?

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

for the AttachDbFilename field, you will see

 "Remote server, HTTP, and UNC path names are not supported"

and "AttachDBFilename is only supported for primary data files with an .mdf extension" ( i have not seen a .mdf extension )

for UNC path names http://msdn.microsoft.com/en-us/library/aa365247(VS.85).aspx

i am not understanding the name of the file ( AttachDbFileName property ) : AttachDbFilename=K:Path Filename.accdb

what is Filename.accdb ? ( i am not a specialist of shared drive , so , i hope you will excuse a question maybe stupid )

Could you tell us from where is coming the .mdf file that you are trying to use ?

A question maybe stupid but when the user instance feature appears with SQL Server 2005, i tried to use it following the advices of several books specialized in SQL Server Express. Thanks to Mike Wachal, i have understood the principles of user instances and i rejected them. I am always using SQL Server Express ( 2005 and 2008 ) without user instances ( i have forbidden the use of user instances on the Server level and i am quiet now ).Moreover, user instance has been depreciated since the release of SQL Server 2008 and normally should be discontinued for the next version after Denali. So you have 3 solutions :

- to continue to use user instances and you will have the risk of impossible use with SQL Server 12 ( 10 = 2008, 11 : Denali ).Don't forget that user instance is a feature suppored by the Express Edition ( unsupported by all other editions like Standard, Web,Workgroup,Entreprise... )

- to replace user instances by the use of SQL Server Compact Edition (CE), it seems that a (secret) wish from Microsoft.

 - you eliminate the use of user instance and you consider the Express Edition like a not free edition with some limits about the size of the database or the absence of the SQL Agent...

Have a nice day

PS : except with the use of SSMS(E) ( SQL Server Management Studio with E for Express for 2008 or EE for Express Edition for 2005 ), it is difficult to know if a datafile ( with .mdf extension ) is belonging ( attached ) to a SQL Server instance.

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 6

I tried using the .mdf extension, but i got similar results.  I'm using the .accdb extension to read the file, but I haven't been able to write to it yet.  .accdb is an MS Access db from MS 2010.  I've looked for another file with the .mdf extension, but have yet to find one.

I shortened the path name to what I put as it was considerably long.  The shared drive is quite large and I have it put into my particular department's folder in the shared drive.

Answer 7

Never mind scrapped the whole, redid it and now it is working.

Answer 8

Hello,

Please, could you give the full connection string that you have used to open a connection to your Access 2010 file ? It would be very kind to share this information for potential visitors having connections problems with an Access 2010 database as in the ConnectionStrings.com site , i have found examples of connection strings only for Access 2003 and 2007.

Thanks beforehand and have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 9

Well like I said I scrapped the whole thing.  I redid it & went with an OLEDB connection string instead of an SQL connection string.

"PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source=K:|Shared Server Directory|Filename.accdb"

Answer 10

I think you'll find this link very helpful as for the code.  I simplified my code even a bit more than what is on this link.

For instance instead of putting the variable declaration, instantiating the variable as a new instance, and setting the command text on 3 seperate lines, I put all that into one line.

http://www.java2s.com/Code/VB/Database-ADO.net/UpdateAccessdatatablethroughOleDbConnection.htm