I have some previous experience with MS Access and VB6 but very new to SQL Server and VB10 so need newbie responses please. I Think SQL Server compact is automatically installed with VB10 but I also installed SQLServer2008 R2 express edition. I created a database using the database explorer functions of Visual Basic 10, and my program connects to and uses the database with no issues. However I am trying to import data from Excel and get an error
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". Number=7302 Procedure="" Server=\.\pipe\EF92A72A-C78F-4C\tsql\query Source=.Net SqlClient Data Provider State=1
I got as far as running the SP to configure ad hoc distributed queries but still have the error. I have searched for a solution to this problem and they all seem to involve using the management studio to allow remote connections. So I tried to do this but failed. When I look at Start>All Programs I have Microsoft SQL Server 2008 and Microsoft SQL Server 2008R2 both present, and management studio is listed under R2. I start management studio and click "Connect" and can't get any further. Under "Server Type" I am offered 'Database Engine' or 'SQL Server Compact' Under "Server Name" I am offered '(Local)' or "IAN' - (my computer's name)
It douesnt matter which permutation of options I choose, I repeatedly get the error message:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider) Microsoft SQL Server, Error 53
(This was trying to connect to IAN - if I try to connect to (Local) the message is identical but the error number is 2 rather than 53) The error message implies I should check that the server is configured to allow remote connections, but how do I do that if I cant connect to the server in the first place?