Solved

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

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

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

828 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