switch form sqlserver to access at runtime

[Tim]

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

 

[Armin Zingler]

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?

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

 

[Tim]

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()"

 

[Cor Ligthert [MVP]]

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

 

[Tim]

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

 

[Patrice]

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

 

[Armin Zingler]

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?

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

 

[Cor Ligthert [MVP]]

Tim,

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

Project
Add Reference
System.configuration

Etc

Cor

 

[vbnetdev]

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

 

[Tim]

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