Andy Lacey
andy at minstersystems.co.uk
Tue Jun 5 09:21:30 CDT 2012
Good info matey cos someone might question my use of ODBC over OLEDB. Plus it gives me all the excuse I need not to waste my time chasing down the OLEDB problem. Catch you soon. Andy -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darren Sent: 05 June 2012 14:59 To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] OLEDB connection string - Thanks 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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com