Dynamically change connection string (different windows/office) ?

SevenDP

New Member
Joined
Mar 10, 2016
Messages
21
Hi guys

please help, my office have some old computers with Win XP & Office 2010 (32bit)
and some new computers with Win 10 & Office 365 (64bit)
the old computers with replaced with the new computers
but we still have old macro in excel, after some research, i found this

Win XP & Office 2010 (32bit) work with this connection string:

Code:
cn_bank.Open "driver=[B]{Microsoft dBase Driver (*.dbf)}[/B];" & _ 
"driverid=277;dbq=" & data_path


but Win 10 & Office 365 (64bit) work with this connection string:

Code:
cn_bank.Open "driver=[B]{Microsoft Access dBase Driver (*.dbf, *.ndx, *.mdx)}[/B];" & _
"driverid=277;dbq=" & data_path


so, my plan is to make that automatically (same macro can run on old and new computers)

my first plan was using on error goto:


Code:
on error goto Load_Driver_2

cn_bank.Open "driver=[B]{Microsoft dBase Driver (*.dbf)}[/B];" & _

"driverid=277;dbq=" & data_path


goto Load_Driver_1_Ok


Load_Driver_2:

cn_bank.Open "driver=[B]{Microsoft Access dBase Driver (*.dbf, *.ndx, *.mdx)}[/B];" & _
"driverid=277;dbq=" & data_path


Load_Driver_1_Ok:
next code ....
next code ....
next code ....



So if got an error with load driver 1, it will try to load driver 2.
But if ok with load driver 1, it will not load driver 2.


But because the macro codes to modified are quite a lot, i want to ask
what event always run once everytime the macro open.
So my plan is, put code in that event, to try which connection string are ok and save it into a variable
then I will change all connection string into that variable




Code:
an event:
dim sConnString as string

on error goto Load_Driver_2
cn_bank.Open "driver=[B]{Microsoft dBase Driver (*.dbf)}[/B];" & _
"driverid=277;dbq=" & data_path

sConnString = "[B]{Microsoft dBase Driver (*.dbf)}[/B]"

goto Load_Driver_1_Ok


Load_Driver_2:

cn_bank.Open "driver=[B]{Microsoft Access dBase Driver (*.dbf, *.ndx, *.mdx)}[/B];" & _
"driverid=277;dbq=" & data_path

sConnString = "[B]{Microsoft Access dBase Driver (*.dbf, *.ndx, *.mdx)}[/B]"

Load_Driver_1_Ok:


so i just need to change all connection string into:


Code:
cn_bank.Open "driver=" & sConnString [B]& "[/B];" & _
"driverid=277;dbq=" & data_path


any suggestion ?

thanks a lot
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Re: How to dynamically change connection string (different windows/office) ?

I think you mean the Workbook_Open event.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top