Solved

On an access2007 databse, switch between a Access backend and SQL backend.

Posted on 2014-09-25
3
369 Views
Last Modified: 2014-10-21
Hi

I currently have DAO code to determine a connection path for a Access backend.  I need to connect to a SQL backend I have not created yet. I was wondering if a connection string can be used similarly for a SQL backend (and Access backend) and assume the DAO code for updating links  can be changed to ADODB?  Can anyone please advise?


Public Const DB_String_Dev          As String = "DSN=MS Access Database;DBQ=C:\Users\MYSELF\Desktop\PUDA_DB_be.accdb;DefaultDir=C:\Users\MYSELF\Desktop;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
Public Const RELEASE_STAGE As String = "DEV"


Public Function DBConVer(DB_Con_Ver As String) As String
DBConVer = DB_String_Dev
End Function

'----------------------------DAO ---Update link module
Private Sub UpdateTblLinks()

Dim CurDB As DAO.Database
Dim tdfLinked As DAO.TableDef
Dim TBDef As DAO.TableDef
Dim strPath As String

On Error GoTo UpdateTblLinks_Err

strPath = DBConVer(RELEASE_STAGE)

Set CurDB = CurrentDb
For Each TBDef In CurDB.TableDefs
    If Len(TBDef.Connect) > 0 Then
        TBDef.Connect = ";Database" & _
        Right(Left(strPath, InStr(strPath, "DefaultDir=") - 2), Len(Left(strPath, InStr(strPath, "DefaultDir=") - 2)) - InStr(strPath, "DBQ=") - 2)
        TBDef.RefreshLink
    End If
UpdateTblLinks_Resume:
Next TBDef

'We've reached the end of the tables, so we can gracefully exit
GoTo UpdateTblLinks_Exit

UpdateTblLinks_Err:
If Err.Number = 3011 Then
    'clear the error and
    Err.Clear
    'resume execution of the loop
    GoTo UpdateTblLinks_Resume
Else
    MsgBox Err.Description
End If

UpdateTblLinks_Exit:
Set CurDB = Nothing
Set tdfLinked = Nothing
Set TBDef = Nothing

End Sub
0
Comment
Question by:yasanthax
  • 2
3 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40343845
You'd have to create a SQL-compliant connection string here:

Public Const DB_String_Dev          As String = "DSN=MS Access Database;DBQ=C:\Users\MYSELF\Desktop\PUDA_DB_be.accdb;DefaultDir=C:\Users\MYSELF\Desktop;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

Note too that you can use DSN-less connections to do this, which would eliminate the need to create/install a DSN on your target machines. It's pretty much what you're doing here, except (of course) you would create a connection string that would not include the DSN.

There are many connection string examples here:

http://www.connectionstrings.com/sql-server/

Yours would be something like:

Public Const DBConn_Live = "Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;"

Then you'd do this:

TBDef.Connect = DConn_Live

In many cases, you'd store this data in a local table, or in a XML or Text file the application could read at startup. This makes changes to that connection quite simple, without having to recompile and redistribute your application.

The above uses the SQL Native Client 10.0. If you're using a different version, you could specify that as well.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40343914
I don't know if it is necessary but in the apps where I support switching between Jet/ACE and SQL Server on the fly,  I delete the current linked tables and then add them again from the other source.  This requires that I keep a local table with the local and remote names of each table I want to link to.  I don't have the code handy but will post it later if your problem isn't resolved.  I had another app where it needed to switch between several development and production databases and since those were all SQL Server, I just kept the connection strings in a table and when the user picked his environment, refreshed them using a loop similar to what you have.
0
 
LVL 84
ID: 40394286
Curious why you awarded a B grade, without any request for additional help? That's not consistent with EE grading policy:

http://support.experts-exchange.com/customer/portal/articles/481419-what-grade-should-i-award-?b_id=44
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now