Solved

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

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

749 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