[AccessD] OLEDB connection string - Thanks

Andy,

I had the same outcome, the OLEODBC never worked reliably but the ODBC did.  Like yourself I gave up 
and just used ODBC.

John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it

On 6/5/2012 4:34 AM, Andy Lacey wrote:
> Thanks to everyone who offered their suggestions and advice. The outcome is
> that I never did get SQLOLEDB working, and I don't know why. I may come back
> to that and take another look. The imperative though was to have something
> distributable without hassle and the ODBC connection using the
> TransferDatabase method has sorted that. Many, many thanks to both Darren
> and Stuart for that.
>
> AccessD rides to the rescue again.
>
> Cheers
>
> Andy
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
> Sent: 04 June 2012 21:15
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] OLEDB connection string - Help please
>
>
> You are correct, you won't need an ODBC data source definition on each pc.
>
> Don't worry about the WorkStation ID, it is not needed.  Just don't put it
> in the connection
> when  linking through code.
>
> Just for illustration, here's some code which lete you switch Back Ends
> easily between a local
> Access BE and  networked Access and SQL Server BEs:   It relies on all
> linked tables having
> a standard prefix  of "tbl"
>
>
> Const strBEName = "NECDecision_BE.mdb"
> Const strServerBEDir = "\\DotSQL\DataApps"
> Const strSQLConnect = "ODBC;DRIVER=SQL
> Server;SERVER=DOTSQL;DATABASE=DoTPolicy;Trusted_Connection=Yes"
>
> Function ConnectSQL() As Long
> Dim tdf As TableDef
> For Each tdf In CurrentDb.TableDefs
>      If Left$(tdf.Name, 3) = "tbl" Then
>           renewlink tdf.Name, strSQLConnect, False
>      End If
> Next
> ConnectSQL = True
> End Function
>
> Function ConnectBELocal() As Boolean
> Dim tdf As TableDef
> If Dir$(CurrentProject.Path&  "\"&  strBENAme")<  " " Then
>       MsgBox "Data file "&  strBEName&  " missing! It must be in the same
> directory as this
> application file.", vbCritical, "ConnectBELocal Failed!"
>       ConnectBELocal = False
>       Exit Function
>   End If
> For Each tdf In CurrentDb.TableDefs
>      If Left$(tdf.Name, 3) = "tbl" Then
>           renewlink tdf.Name, CurrentProject.Path&  "\"&  strBEName, True
>      End If
> Next
> ConnectBELocal = True
> End Function
>
> Function ConnectServerBE() As Long
> Dim tdf As TableDef
> If Dir$(strServerBEDir&  "\"&  strBEName)<  " " Then
>       MsgBox "Data file "&  strBEName&  " missing! It must be in the "&
> strServerBEDir&  ".",
> vbCritical, "ConnectBELocal Failed!"
>       ConnectServerBE = False
>       Exit Function
>   End If
> For Each tdf In CurrentDb.TableDefs
>      If Left$(tdf.Name, 3) = "tbl" Then
>           renewlink tdf.Name, strServerBEDir&  "\"&  strBEName, True
>      End If
> Next
> ConnectServerBE = True
> End Function
>
> Function renewlink(tablename As String, datafile As String, AccessDb As
> Boolean) As Long
> On Error Resume Next
> DoCmd.DeleteObject acTable, tablename
> On Error GoTo 0
> Select Case AccessDb
> Case True
> DoCmd.TransferDatabase acLink, "Microsoft Access", datafile, acTable,
> tablename,
> tablename, False
> Case False
> DoCmd.TransferDatabase acLink, "ODBC Database", datafile, acTable,
> tablename,
> tablename, False
> End Select
> End Function
>
>