[AccessD] OLEDB connection string - Thanks

Darren darren at activebilling.com.au
Tue Jun 5 08:58:47 CDT 2012


Andy - I forgot to mention this when we were skyping tonight.
OLEDB is to be deprecated by MSoft anyway - though you have a few years :-)

http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/e696d0ac-
f8e2-4b19-8a08-7a357d3d780f

see ya from the antipodes

D

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Andy Lacey
Sent: Tuesday, 5 June 2012 9:15 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] OLEDB connection string - Thanks

Hi JC

Thanks for that. It'll save me some frustrating time. If you didn't get it
working what chance do I stand so I'll leave it at that.

Cheers
Andy

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: 05 June 2012 12:01
To: Access Developers discussion and problem solving
Subject: Re: [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
>
>

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list