Solved Can't find System DSN after upgrading to Office 365

[Tbure90]

I was recently forced by my IT group to upgrade to Office 365. I had an issue with Excel and they "accidentally" deleted Microsoft Office and claimed that there was no way to reinstall my old version of Office and had to upgrade.....

I didn't want to upgrade to Office 365 precisely because of stuff like this.

I had a database with a bunch of ODBC connections to our MRP system that uses Foxpro .dbf tables to store all our data. I was using the Foxpro VFP Driver (version 6.01.8629.01) and created a system DSN just for my own personal use to be able to view these tables. My boss wanted me to look at one of the tables because we think a stockroom transaction was entered incorrectly and is corrupting our inventory quantities.

I hadn't used this database since before I was forced to upgrade and now all of a sudden I get the following error when trying to open one of the tables:

I thought okay, maybe i just need to delete the table and try to reconnect. Went to create a new ODBC data source and now my System DSN (pcmrpVFP) won't show up anymore (see below). It used to show up here, I would double click and all all the .dbf tables in the free table directory would show up

When I open the ODBC Data Source Administrator my system DSN shows up so it does still exist.

Is this a known issue with Office 365? If so, does someone have a solution or workaround so that I can connect to those tables again?

 

[Minty]

What happens if you click New in the Select data source?

I think it will give you an option to use any of your existing ODBC Sources to create a new Machine Data Source

 

[Tbure90]

What happens if you click New in the Select data source?

I think it will give you an option to use any of your existing ODBC Sources to create a new Machine Data Source

I get the following message

Which is strange because I do have administrative privileges. It then allows me to create a user DSN but the driver won't show up. My Access is 64-bit and the DSN is 32-bit. I'm 99% sure that my Access version was 64-bit before i upgraded to 365 so not sure if that's the issue.

 

[sonic8]

I'm 99% sure that my Access version was 64-bit before i upgraded to 365 so not sure if that's the issue.

Unfortunately your screenshots prove you wrong. Access 64bit cannot see/use any 32bit DSN or driver. Your screenshots show that these DSNs are 32bit only. It is impossible that you previously had 64bit Access and were using these DSNs.

 

[sonic8]

Which is strange because I do have administrative privileges.

I can reproduce this issue; not sure what is causing it.
Workaround:
Run CMD as Administrator
Then open the ODBC Admin tool from the command line: %windir%\system32\odbcad32.exe

 

[adhoustonj]

Some ODBC drivers can also be bit specific. I have to connect to an older IBM database and the only driver my IT could find was 32 bit. When I got a new 64 bit laptop, they gave me 64 bit Office, but I had to change it back to 32 bit Office just to be able to still connect to the IBM db via the 32 bit driver.

 

[Tbure90]

Some ODBC drivers can also be bit specific. I have to connect to an older IBM database and the only driver my IT could find was 32 bit. When I got a new 64 bit laptop, they gave me 64 bit Office, but I had to change it back to 32 bit Office just to be able to still connect to the IBM db via the 32 bit driver.

Looks like that's the only option here. I opened my old laptop with windows 7, 32-bit access and created a new DSN and it worked just fine.

 

[gemma-the-husky]

Office used to install as 32bit by default, but it's changed to installing as 64bit.

You could reinstall as 32bit, and see if that fixes the FoxPro issue, but it does beg the question as to how your other databases are being used. If they are compiled to accde versions, then users might not be able to run some databases because of the incorrect bitness.

It looks like you've fixed the problem for the moment anyway.

 

[sonic8]

Some ODBC drivers can also be bit specific.

Thank you very much for this important comment. I thought this was obvious and thus didn't mention it.
BTW: *All* ODBC drivers are bit specific by their very nature as native DLLs. Some driver vendors obscure this fact by bundling the 32bit and 64bit edition of their drivers in one generic setup that installs both of them. So, it sometimes appears that there is a platform independent driver, while in reality there are two drivers bundled together.

Looks like that's the only option here.

There are 3rd-party 64bit ODBC drivers for Visual FoxPro available.

• devart - ODBC Driver for xBase
• cdata - xBase ODBC Driver

I never used either driver, but I own and use other products from devart and the product quality and their support is excellent.

 

[Tbure90]

Thank you very much for this important comment. I thought this was obvious and thus didn't mention it.
BTW: *All* ODBC drivers are bit specific by their very nature as native DLLs. Some driver vendors obscure this fact by bundling the 32bit and 64bit edition of their drivers in one generic setup that installs both of them. So, it sometimes appears that there is a platform independent driver, while in reality there are two drivers bundled together.


There are 3rd-party 64bit ODBC drivers for Visual FoxPro available.

I never used either driver, but I own and use other products from devart and the product quality and their support is excellent.

I'll take a look a those, i had heard about devart but couldn't find a download file anywhere, thanks! Are these bundled as 32-bit and 64-bit or just 64-bit? (I'm assuming the latter). Also it appears these are for xBase, you sure they will work for Foxpro?

Office used to install as 32bit by default, but it's changed to installing as 64bit.

You could reinstall as 32bit, and see if that fixes the FoxPro issue, but it does beg the question as to how your other databases are being used. If they are compiled to accde versions, then users might not be able to run some databases because of the incorrect bitness.

It looks like you've fixed the problem for the moment anyway.

I use .mdb versions for everything. I'm the only one who uses the foxpro driver as of now for convenience. All the databases i've built for everyone requires them to dump data out of our MRP system into excel files. This is why I'm hoping the 3rd party 64-bit drivers work so people won't have to downgrade to 32-bit office because eventually i want to change all the databases to use the driver to link directly to the MRP system's tables.

 

[sonic8]

Are these bundled as 32-bit and 64-bit or just 64-bit? (I'm assuming the latter). Also it appears these are for xBase, you sure they will work for Foxpro?

As mentioned, I didn't use that driver myself.

The vendor lists 32bit and 64bit compatibility on the features list. So, I assume it is a bundle supporting both.

"xBase" is a summary term for multiple database applications using a dBase file format. Visual FoxPro support is explicitly listed in the features list.

 

[Tbure90]

As mentioned, I didn't use that driver myself.

The vendor lists 32bit and 64bit compatibility on the features list. So, I assume it is a bundle supporting both.

"xBase" is a summary term for multiple database applications using a dBase file format. Visual FoxPro support is explicitly listed in the features list.

Correct I probably should've opened the link and read that myself before asking you

I was hoping they'd be free, Devart has much more reasonable pricing. cdata pricing is ridiculous imo. Either way thanks for sharing, i will keep those in my back pocket if we ever decide to go that route.

For now I'll just use my old laptop with 32-bit Access/Office.

 

[gemma-the-husky]

I'll take a look a those, i had heard about devart but couldn't find a download file anywhere, thanks! Are these bundled as 32-bit and 64-bit or just 64-bit? (I'm assuming the latter). Also it appears these are for xBase, you sure they will work for Foxpro?



I use .mdb versions for everything. I'm the only one who uses the foxpro driver as of now for convenience. All the databases i've built for everyone requires them to dump data out of our MRP system into excel files. This is why I'm hoping the 3rd party 64-bit drivers work so people won't have to downgrade to 32-bit office because eventually i want to change all the databases to use the driver to link directly to the MRP system's tables.

If you use mdbs it's a lot easier. You can build with conditional options to select code that is compatible with A32 or A64 and have that resolved at run time. That won't compile to an mde, but it does mean you can have a single database that will work with both versions of access.

 

[Tbure90]

If you use mdbs it's a lot easier. You can build with conditional options to select code that is compatible with A32 or A64 and have that resolved at run time. That won't compile to an mde, but it does mean you can have a single database that will work with both versions of access.

So basically on database open, run some code to find the bit-version of Access then programmatically create a connection string for the DSN with the matching bit-version?

 

[gemma-the-husky]

You can have conditional compiler directives something like this below (Offhand aircode) Others will know the precise syntax. The code will work correctly at run time, and resolve the bitness.

The trouble is that you can't build this into an mde/accde and have it work in both bitnesses, as it won't compile in either bitness.

If #64bit then
64 bit code
Else
32 bit code
End if

So yes, at the appropriate point to create the connection/DSN you can have code that will run correctly in either bitness.

 

[sonic8]

So basically on database open, run some code to find the bit-version of Access then programmatically create a connection string for the DSN with the matching bit-version?

You could do that but you might not even need to.
Just name the 32bit and 64bit DSN exactly the same and and your application will work in both bitnesses without requiring a single line of code dependent on the bitness.
Even on a single computer 32bit and 64bit ODBC drivers and DSNs can coexist with the exact same name.

Only if you want to use different ODBC drivers for 32bit and 64bit, you potentially must adjust the connections string. - I would rather discourage this approach because two different drivers might have subtle differences in their behavior which might cause hard to detect problems.

 

[sonic8]

You can have conditional compiler directives something like this below (Offhand aircode) Others will know the precise syntax. The code will work correctly at run time, and resolve the bitness.

There is a subtle error in these introductory sentences. Compiler constants will not resolve bitness at run time but at compile time. When distributing an mdB/accdB, compilation might happen immediately before execution, but they should still be viewed as different subsequent processes.

@Tbure90 (maybe!) needs to differentiate between 32bit and 64bit at runtime when connecting to the VFP database. So, there is no need for compile time detection of the bitness.
An easy way to detect bitness at runtime is to check the size of a LongPtr variable using the LenB function. If its size is 4 bytes, you are on 32bit, if it is 8 bytes you are on 64bit.

 

[gemma-the-husky]

Maybe I'm wrong. What I thought was it's possible to have one mdb that will run correctly in either bitness of access, but it won't compile to an mde that will do the same. That's what I meant anyway.

 

[sonic8]

What I thought was it's possible to have one mdb that will run correctly in either bitness of access, but it won't compile to an mde that will do the same.

That's perfectly correct!
And "compile to an mde" is the explanation why compiler constants, which are applied during that process, don't have an effect after the compilation process.
I only nitpicked on this because in the context of this thread it's particularly important to distinguish compile time and run time.