[AccessD] OLEDB connection string - Thanks

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
 

-- 
Stuart


On 4 Jun 2012 at 17:02, Andy Lacey wrote:

> Stuart, I could kiss you (it's ok I won't). That's it. Hallelujah.
> 
> So, I've still failed to get OLEDB to work but I think I have an ODBC
> connection which does not require an ODBC Data Source defining on the pc.
Is
> that right? One thing, if I open the table in design view and look at the
> connection string it has a WSID parameter with my pc's name in it. Is that
> going to bite me when this is distributed?
> 
> Andy 
> 
> 

--
Stuart McLachlan

Ph:    +675 340 4392 
Mob: +675 7100 2028
Web: http://www.lexacorp.com.pg

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