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
CompanyTableAda pter.Connection .ConnectionStri ng = nS

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

Dim nS = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & dbname
CompanyTableAda pter.Connection .ConnectionStri ng = 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]

Re: switch form sqlserver to access at runtime

"Tim" <Citizen10Bears @gmail.com> schrieb[color=blue]
> 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
> CompanyTableAda pter.Connection .ConnectionStri ng = nS
>
> I was hoping to be able to change to an access database using the
> following:
>
> Dim nS = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & dbname
> CompanyTableAda pter.Connection .ConnectionStri ng = 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?[/color]


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

Try this:

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

See also: www.connectionstrings.com


Armin

[Tim]

Re: switch form sqlserver to access at runtime

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.Com mon.DbConnectio nOptions.ParseI nternal(Hashtab le
parsetable, String connectionStrin g, Boolean buildChain, Hashtable
synonyms, Boolean firstKey)
at System.Data.Com mon.DbConnectio nOptions..ctor( String
connectionStrin g, Hashtable synonyms, Boolean useOdbcRules)
at System.Data.Sql Client.SqlConne ctionString..ct or(String
connectionStrin g)
at
System.Data.Sql Client.SqlConne ctionFactory.Cr eateConnectionO ptions(String
connectionStrin g, DbConnectionOpt ions previous)
at
System.Data.Pro viderBase.DbCon nectionFactory. GetConnectionPo olGroup(String
connectionStrin g, DbConnectionPoo lGroupOptions poolOptions,
DbConnectionOpt ions& userConnectionO ptions)
at System.Data.Sql Client.SqlConne ction.Connectio nString_Set(Str ing
value)
at System.Data.Sql Client.SqlConne ction.set_Conne ctionString(Str ing
value)
at BluebaseAttach. mainform.mainfo rm_Load(Object sender, EventArgs e)
in C:\Documents and Settings\TIM\My Documents\Visua l Studio
2005\Projects\B luebaseAttach\B luebaseAttach\m ainform.vb:line 106
at System.EventHan dler.Invoke(Obj ect sender, EventArgs e)
at System.Windows. Forms.Form.OnLo ad(EventArgs e)
at System.Windows. Forms.Form.OnCr eateControl()
at System.Windows. Forms.Control.C reateControl(Bo olean
fIgnoreVisible)
at System.Windows. Forms.Control.C reateControl()
at System.Windows. Forms.Control.W mShowWindow(Mes sage& m)
at System.Windows. Forms.Control.W ndProc(Message& m)
at System.Windows. Forms.Scrollabl eControl.WndPro c(Message& m)
at System.Windows. Forms.Container Control.WndProc (Message& m)
at System.Windows. Forms.Form.WmSh owWindow(Messag e& m)
at System.Windows. Forms.Form.WndP roc(Message& m)
at
System.Windows. Forms.Control.C ontrolNativeWin dow.OnMessage(M essage& m)
at System.Windows. Forms.Control.C ontrolNativeWin dow.WndProc(Mes sage&
m)
at System.Windows. Forms.NativeWin dow.DebuggableC allback(IntPtr hWnd,
Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows. Forms.SafeNativ eMethods.ShowWi ndow(HandleRef hWnd,
Int32 nCmdShow)
at System.Windows. Forms.Control.S etVisibleCore(B oolean value)
at System.Windows. Forms.Form.SetV isibleCore(Bool ean value)
at System.Windows. Forms.Control.s et_Visible(Bool ean value)
at
System.Windows. Forms.Applicati on.ThreadContex t.RunMessageLoo pInner(Int32
reason, ApplicationCont ext context)
at
System.Windows. Forms.Applicati on.ThreadContex t.RunMessageLoo p(Int32
reason, ApplicationCont ext context)
at System.Windows. Forms.Applicati on.Run(Applicat ionContext context)
at
Microsoft.Visua lBasic.Applicat ionServices.Win dowsFormsApplic ationBase.OnRun ()
at
Microsoft.Visua lBasic.Applicat ionServices.Win dowsFormsApplic ationBase.DoApp licationModel()
at
Microsoft.Visua lBasic.Applicat ionServices.Win dowsFormsApplic ationBase.Run(S tring[]
commandLine)
at BluebaseAttach. My.MyApplicatio n.Main(String[] Args) in
17d14f5c-a337-4978-8281-53493378c1071.v b:line 81
at System.AppDomai n.nExecuteAssem bly(Assembly assembly, String[]
args)
at System.AppDomai n.ExecuteAssemb ly(String assemblyFile, Evidence
assemblySecurit y, String[] args)
at Microsoft.Visua lStudio.Hosting Process.HostPro c.RunUsersAssem bly()
at System.Threadin g.ThreadHelper. ThreadStart_Con text(Object state)
at System.Threadin g.ExecutionCont ext.Run(Executi onContext
executionContex t, ContextCallback callback, Object state)
at System.Threadin g.ThreadHelper. ThreadStart()"

[Cor Ligthert [MVP]]

Re: switch form sqlserver to access at runtime

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" <Citizen10Bears @gmail.com> schreef in bericht
news:1140524413 .497342.98340@z 14g2000cwz.goog legroups.com...[color=blue]
> 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
> CompanyTableAda pter.Connection .ConnectionStri ng = nS
>
> I was hoping to be able to change to an access database using the
> following:
>
> Dim nS = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & dbname
> CompanyTableAda pter.Connection .ConnectionStri ng = 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
>[/color]

[Tim]

Re: switch form sqlserver to access at runtime

thanks,

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

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

thanks

[Patrice]

Re: switch form sqlserver to access at runtime

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/de...vsgenerics.asp

--
Patrice

"Tim" <Citizen10Bears @gmail.com> a écrit dans le message de
news:1140524413 .497342.98340@z 14g2000cwz.goog legroups.com...[color=blue]
> 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
> CompanyTableAda pter.Connection .ConnectionStri ng = nS
>
> I was hoping to be able to change to an access database using the
> following:
>
> Dim nS = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & dbname
> CompanyTableAda pter.Connection .ConnectionStri ng = 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
>[/color]

[Armin Zingler]

Re: switch form sqlserver to access at runtime

"Tim" <Citizen10Bears @gmail.com> schrieb[color=blue]
> 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?[/color]


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...onnections.asp


Armin

[Cor Ligthert [MVP]]

Re: switch form sqlserver to access at runtime

Tim,

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

Project
Add Reference
System.configur ation

Etc

Cor


"Tim" <Citizen10Bears @gmail.com> schreef in bericht
news:1140533749 .976490.186540@ g43g2000cwa.goo glegroups.com.. .[color=blue]
> thanks,
>
> I get an error on this line...
> Private ConnSettings As New ConnectionStrin gSettings
>
> is this because I am missing the dbfactory class you mention?
> I just copied the code straight from your webpage.
>
> thanks
>[/color]

[vbnetdev]

Re: switch form sqlserver to access at runtime

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

--
Get a powerful web, database, application, and email hosting with KJM
Solutions

http://www.kjmsolutions.com

"Tim" <Citizen10Bears @gmail.com> wrote in message
news:1140524413 .497342.98340@z 14g2000cwz.goog legroups.com...[color=blue]
> 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
> CompanyTableAda pter.Connection .ConnectionStri ng = nS
>
> I was hoping to be able to change to an access database using the
> following:
>
> Dim nS = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & dbname
> CompanyTableAda pter.Connection .ConnectionStri ng = 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
>[/color]

[Tim]

Re: switch form sqlserver to access at runtime

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

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

thanks

Tim