Solved

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

Posted on 2014-09-25
3
365 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

708 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

17 Experts available now in Live!

Get 1:1 Help Now