Dynamically change connection string (different windows/office) ?

[SevenDP]

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:

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:

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:

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

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:

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

any suggestion ?

thanks a lot

 

[theBardd]

Re: How to dynamically change connection string (different windows/office) ?

I think you mean the Workbook_Open event.

 

[SevenDP]

Re: How to dynamically change connection string (different windows/office) ?

I think you mean the Workbook_Open event.

Ok let my try this, thanks theBardd

 

[SevenDP]

Re: How to dynamically change connection string (different windows/office) ?

I think you mean the Workbook_Open event.

It works
Thanks theBardd