?
Solved

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

Posted on 2014-09-25
3
Medium Priority
?
379 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1500 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 38

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 85
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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

765 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