SQL connection verification when opening database

[mcelligott]

Background: I have a Access database. When I open it currently it tests to make sure (via VBA) it sees the back-end mdb data file. If it doesn't, it connects to and relinks the tables to an alternate back-end mdb data file (a local copy). Because we rely on this database for critical information, it must be up 99.999% of the time, that is why I have built it with the redundancy. It has been very successful, especially on the couple of occasions we have lost our network.

What I am attempting to do: Because we use this database constantly and have so much information contained in it, the size after compact and repair is growing (900 mb). I would like stay ahead of things and move it to a SQL back-end so size will not be an issue if it should approach the max size (2gb for Access). While I figured out how to move the back-end data to SQL and connect it to the front-end database, I would still like to build in the redundancy I have in the current version. Unfortunately I am not familiar with how to test if it sees the new SQL back-end via VBA.

Has anyone attempted to do this or have suggestions on how to do this? Any help would be greatly appreciated.

Thanks in advance,

Bob

 

[dhookom]

I think you could create a simple pass-through query to the SQL database and attempt to open it as a recordset. Capture any error which will indicate an issue with the network.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[mcelligott]

Hi Duane,

Unfortunately I am unfamiliar with first how to test to make sure the front end sees the SQL backend. And second once it does see it again, to programmatically (VBA) re-coonect the tables to the SQL back end. I do this currently for my Microsoft Access backend but that is simple. I can make the connections manually to the SQL back end but am trying to automate it so if things go wrong while I am not here, it will work automatically. Do you have any examples?

Thanks in advance for any help you can offer,

Bob

 

[dhookom]

How do you make sure your front-end can see the back-end in Access? I use a variation of Doug Steele's code for DSN-less connections.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[mcelligott]

When my front end opens, it first looks to see if it can see the back end file on the network (If FSO.FileExists(strServerPath) = True). If it does (true) then it makes sure that the tables are linked to that source. If it does not see the back end on the primary server, it looks for a back end on the alternate server. If it sees the alternate server, it relinks all the tables to that server (and also warns the user that it is not the primary). If it can not find a back end on the alternate server, it looks for a local copy of the back end on their computer. It then relinks all the tables to the local copy (and also warns the user that it is a local copy only and may not be up-to-date).

The redundancy is built in because we must be up 99.999% of the time. The nature of our business depends on the database being available. We have lost our primary server or entire network over the years. This method of detection and relinking has worked for us and kept us running. I would like to keep the redundancy. I realize the primary server will be SQL and the alternates (both the alternate server and local copy) will be mdb.

Hope that answers your question and explains what I am trying to do. I am trying to make sure I can maintain the redundancy even with a SQL back end. I am definitely into a realm that I need to learn about (and am willing to) but it is going to take time for me but am attempting to move this process along as so we can move to SQL. I am trying to read through the link you sent but it is taking time to digest what it is saying.

I do appreciate your response and helping me in any way you can.

Thanks,

Bob

 

[dhookom]

The link that I sent was only for creating or refreshing the links. If your only SQL source is the primary then you shouldn't need it.

I would attempt to open a recordset based on the SQL database. If the code errors, there is an issue connecting which could be a network error or something with the SQL Server. Your code could then proceed as it has in the past with notification and linking to other sources.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016