Hi, I hope this is an easy question for you guys...

I have an application that I need to be able to switch from sqlserver
to access at runtime.

I can change from one sqlserver installation to another using this
code...

Dim nS = "Data Source=" & Server & ";Initial Catalog=" & DatabaseName &
";Persist Security Info=True;User ID=" & UserName & ";Password=" &
Password
CompanyTableAdapter.Connection.ConnectionString = nS

I was hoping to be able to change to an access database using the
following:

Dim nS = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbname
CompanyTableAdapter.Connection.ConnectionString = nS

but whatever access string I try I get errors, such as "keyword
'Provider' not recognised"

can anyone point me in the right direction please?

thanks

Tim

Show quote Hide quote This is /exactly/ your source code? Hard to believe if you get this error
message.

Try this:

Dim nS As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbname

See also: www.connectionstrings.com


Armin

does this stack trace help?

does it matter how the original datasource was configured at design
time?
I used the datasource configuration wizard and used "microsoft sql
server" as the datasource. Is this why I can't change it to access?


(stack trace follows)
"   at System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable
parsetable, String connectionString, Boolean buildChain, Hashtable
synonyms, Boolean firstKey)
   at System.Data.Common.DbConnectionOptions..ctor(String
connectionString, Hashtable synonyms, Boolean useOdbcRules)
   at System.Data.SqlClient.SqlConnectionString..ctor(String
connectionString)
   at
System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String
connectionString, DbConnectionOptions previous)
   at
System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(String
connectionString, DbConnectionPoolGroupOptions poolOptions,
DbConnectionOptions& userConnectionOptions)
   at System.Data.SqlClient.SqlConnection.ConnectionString_Set(String
value)
   at System.Data.SqlClient.SqlConnection.set_ConnectionString(String
value)
   at BluebaseAttach.mainform.mainform_Load(Object sender, EventArgs e)
in C:\Documents and Settings\TIM\My Documents\Visual Studio
2005\Projects\BluebaseAttach\BluebaseAttach\mainform.vb:line 106
   at System.EventHandler.Invoke(Object sender, EventArgs e)
   at System.Windows.Forms.Form.OnLoad(EventArgs e)
   at System.Windows.Forms.Form.OnCreateControl()
   at System.Windows.Forms.Control.CreateControl(Boolean
fIgnoreVisible)
   at System.Windows.Forms.Control.CreateControl()
   at System.Windows.Forms.Control.WmShowWindow(Message& m)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
   at System.Windows.Forms.ContainerControl.WndProc(Message& m)
   at System.Windows.Forms.Form.WmShowWindow(Message& m)
   at System.Windows.Forms.Form.WndProc(Message& m)
   at
System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&
m)
   at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd,
Int32 msg, IntPtr wparam, IntPtr lparam)
   at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd,
Int32 nCmdShow)
   at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
   at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
   at System.Windows.Forms.Control.set_Visible(Boolean value)
   at
System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32
reason, ApplicationContext context)
   at
System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32
reason, ApplicationContext context)
   at System.Windows.Forms.Application.Run(ApplicationContext context)
   at
Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
   at
Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
   at
Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[]
commandLine)
   at BluebaseAttach.My.MyApplication.Main(String[] Args) in
17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
   at System.AppDomain.nExecuteAssembly(Assembly assembly, String[]
args)
   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence
assemblySecurity, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.Run(ExecutionContext
executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()"

If you are using a component that is there to access the Microsoft SQL
Server, you won't have success in accessing an Access database with it
just by changing the connection string. I thought you know this because
you were using it.

Have a look here:
http://msdn.microsoft.com/library/en-us/cpguide/html/cpconadonetconnections.asp


Armin

Tim,

If you are using VB 2005 than the dbfactory class could be your solution.

Have a look at this sample at our website.
http://www.vb-tips.com/default.aspx?ID=8c3dc2d7-1232-4dd1-817e-22eaaebb2723

I hope this helps,

Cor

Show quoteHide quote

thanks,

I get an error on this line...
Private ConnSettings As New ConnectionStringSettings

is this because I am missing the dbfactory class you mention?
I just copied the code straight from your webpage.

thanks

Tim,

Did you do this as told in this sample
'Set a reference to System.Configuration

Project
Add Reference
System.configuration

Etc

Cor


Show quoteHide quote

no, I hadn't done that!
I presumed that the line "Imports System.Configuration" did this for
me.

I made the reference as described and the code has been very useful.

thanks

Tim

Looks like you are using the SqlClient namespace. In this case the
"Provider" has no meaning as you can't access anything else than SQL
Server...

If yes, you'll have to make some changes in your application to be able to
support this (likely easier with 2.0). Try:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/vsgenerics.asp

Show quoteHide quote

http://www.kjmsolutions.com/flexibleconnectionstring.htm

Show quote Hide quote

Address parameter in WebClient.UploadFile?
Program flow control
Current Recordset does not support updating - HELP needed!
Which Database type to use?
Unable to open file remotely
Simple Binding with Textbox Control
mshflexgrid Problem
system.runtime.interopservices.COMException
Countdown Timer
VB 2005