ODBC Connection setup using DBA Mfg and Access 2013 with a 64 bit computer.

I work at a small manufacturing company that has an ERP called DBA Manufacturing.  It is a SQL Firebird flat relational database.  I thought I understood what that meant at some point, but I am finding that this database is set up with primary keys and foreign keys in the tables just like my Access works.  So I really don't understand the "flat" at all.

I have tried to set up the connection several times and at some point I had the 32 bit connection to DBA testing successful in the ODBC Data Source Administrator.  At that time I had uninstalled the 64 bit driver.  As soon as I reinstalled that driver I went back and tested the 32 bit connection and it failed.  I can see both drivers in the program list in Control Panel/Uninstall programs, but the have exactly the same name.  When I look at the driver list in the DSA it only shows one driver.

Everything I can find on this says that the Access needs to use the 64 bit driver and the DBA needs to use the 32 bit driver.  How do I get them to do that?  A programmer friend of mine says I should look in the Access program settings for a specific driver setting.
Robert MandrellAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick UpsonPrincipal Operations EngineerCommented:
the 64 bit driver should work for anything so long as the firebird server program is 64 bit,

firebird is not a 'flat' database at all, everything is stored in a single file (usually) but the file is highly organised and not human-readable

firebird has keys, constraints, trigger, stored procedures, views and many more features
Robert MandrellAuthor Commented:
The firebird server program is 32 bit.  My boss has a 32 bit Dell computer and the ODBC works fine on his machine with the same settings and Access 2013.  The only difference we know of is that my computer is a 64 bit machine.
Nick UpsonPrincipal Operations EngineerCommented:
is the firebird database file hosted on the first one and on a network share to the second, that won't work

is the firewall on the first computer adjusted to permit access
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Robert MandrellAuthor Commented:
I sent an email to the IT manager about the database file.  My boss is on vacation so I can't look at his settings as far as the firewall goes.  I looked at mine and there is nothing about the database or the ODBC drivers on the allowed programs list.  We are using the windows firewall.

I do log onto the database regularly though and the query you helped me with helped considerably with the project I am on.  I work on a wired connection to the server when I am in the office and through a VPN when I am working from home or elsewhere.  I am leaving Tulsa to go on vacation in the morning and I will be using the VPN for the next week.
Nick UpsonPrincipal Operations EngineerCommented:
so the database is on bosspc and you already access it from yourpc ? but now you are trying to do the same thing but using odbc instead?
Robert MandrellAuthor Commented:
Sorry, the database is on the server.  We both access the database on the server with our individual PCs.  I can do what I want with queries or ODBC.  I understand that the tools I develop in Access will update somewhat automatically if I use the ODBC.

I just got the following reply back from the IT manager:

They are both hosted on the server. The firebird program and the shares. The firewall is open otherwise DBA wouldn't work
Nick UpsonPrincipal Operations EngineerCommented:
are you trying to access the database with a connection string like this
x:\\shared_drives\database\mydb.fdb

or
servername:\databases\thedb.fdb

accessing as a shared file will not work and is likely to corrupt the database if you manage to force it

firebird is intended to be used as a client-server database, your application talks to the firebird server process on the server, which talks to the actual database file.
Robert MandrellAuthor Commented:
Our IT manager has told me before that the DBA Mfg is a client to the firebird database.

I am using the ODBC Data Source Administrator,  System DSN tab as instructed in the attached PDF file.  Right now I have both the 64 and 32 bit drivers installed and the connection fails.  I had it testing successful with these settings without the 64 bit driver installed but Access would not connect when I tried to download the tables as a new database.
ODBC-Screen-Shot.docx
DBA-Mfg-Support-Center-ODBC-Setup-Guide.
Nick UpsonPrincipal Operations EngineerCommented:
< Our IT manager has told me before that the DBA Mfg is a client to the firebird database.
that makes sense, you are trying to setup another client (odbc / access)

I notice the pdf says to only use the 32-bit odbc

if you follow the instructions (pay extra care to the parts about server names and paths) and only have the 32 bit - is it ok when you press the test connection button - you seem to say it is.

If it does test ok with 32bit drivers then do you have the 64bit access installed, if so I suspect this is the issue 32bit odbc + 64bit access
Robert MandrellAuthor Commented:
Yes, that is the closest I have come to the connection working.  It just occurred to me that I might be able to specify a 32 bit at installation of the MS Access.
Robert MandrellAuthor Commented:
I have been out on vacation but while I was on vacation I installed the 32 bit version of MS Access.

Now I am back to the Data Source Administrator.  I had the settings correct in this 32 bit DSA at one time and they were the same or very close to what I have now.  I asked for help from DBA Mfg tech support and they tell me the problem is that I do not have the database server name completely in the database setting.  When I had the connection testing successfully before, this is the setting that i had.

So I think the problem has to do with a firewall setting or something that prevents the DSA from seeing the database.  I have attached a screen image of what I see when I try to browse to the database location on the server.  I manually typed in the database location since I could not go directly to it and pick it.
072715-SS-of-ODBC-Setup.docx
Nick UpsonPrincipal Operations EngineerCommented:
I can only repeat what I said already, you MUST NOT connect via a shared or network drive



are you trying to access the database with a connection string like this
x:\\shared_drives\database\mydb.fdb

or
servername:\databases\thedb.fdb

accessing as a shared file will not work and is likely to corrupt the database if you manage to force it

firebird is intended to be used as a client-server database, your application talks to the firebird server process on the server, which talks to the actual database file.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Robert MandrellAuthor Commented:
Although Nick helped me figure out how to get the connection to work i am still confused as to whether I might be corrupting the database by using the ODBC.  The technical help at DBA Manufacturing assures me that I am not as long as the connection is "one way" or in other words there is no modification of the database coming from the connection.

I wanted the connection so that I could simplify some of the supply chain tasks that I need to do which requires no data modifications back to the DBA Manufacturing database.  All of the input to DBA Mfg will be made through DBA input conventions designed into the database.
Nick UpsonPrincipal Operations EngineerCommented:
there is the chance you could corrupt the data by changing data via odbc or any other form of connection, however if you just read the data this should not have any such risk

BTW: beware starting a transaction (in your case a database access and leaving it open for a long time, hours or more, without going into lots of details this can cause the database to slow down.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.