11-11-2008 02:06 AM
11-13-2008 12:50 PM
03-26-2009 08:47 AM
Mike,
How did you solve this issue?
03-27-2009 10:02 AM
While we found a way to set the ActReader pass in cade, we ended up finding a better way to grab the data we needed.
What are you trying to achieve?
03-27-2009 11:41 AM
I have a MSAccess reporting application that takes a snap-shot of the ACT Opportunity data every month (needs a historical snap-shot of the data at the time) and then generates reports.
The first time into the database, the application fails because the application is not logged on to the ACTReader DSN ODBC connection. I could do this manually but would like to have it automated through code.
If I logon manually, the application operates as desired. I am looking at OpenDataBase and other methods but so far without success.
Thanks,
Jim
03-27-2009 11:54 AM
The code we had wouldn't help... it just sets the pass - we wanted to have an app connect to ACT! for a client with 100 sync subscriber databases and the AR is machine (instance) specific.
I've heard about your issue... can't remember the cause now, but it was a setting in Access to save the user/pass.
Maybe this: SQL ODBC connection strings
Standard Security:
"Driver={SQLServer};Server=Your_Server_Name;Database=Your_Database_Name;Uid=Your_Username;Pwd=Your_Password;"
Trusted connection:
"Driver={SQLServer};Server=Your_Server_Name;Database=Your_Database_Name;Trusted_Connection=yes;"
Failing that, what version of ACT!?
03-27-2009 12:08 PM
ACT 11.1 - I will see if I can find a setting in ACT to make the Id and Psw persistent.
Jim
03-27-2009 12:36 PM
Once you've run ActReader.exe (only available in Premium version), you will have set the Id/Psw
It's the code in Access that needs to remember it
04-02-2009 09:15 AM
I have solved this issue by creating a module that executes when you open the database. Note that this is not a multiuser database and the code could be optimized to see if the application is logged on to SQLServer/ACTREADER and not execute if already logged on.
An Access table (tblACTREADERLogonInfo) stores the database name, server name and the SQLServer Id and Password I established for ACTREADER.
Public Sub SQLServerLogon()
Dim rstRecordSet As ADODB.Recordset
Set rstRecordSet = New ADODB.Recordset
rstRecordSet.CursorLocation = adUseClient
SQLString = ""
SQLString = SQLString + "SELECT tblACTREADERLogonInfo.* FROM tblACTREADERLogonInfo WHERE (((tblACTREADERLogonInfo.Id)=1));"
rstRecordSet.Open SQLString, CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
Do Until rstRecordSet.EOF
Dim mstrODBCConnect As String
Dim fLink As Boolean
Dim tdf As DAO.TableDef
Dim db As DAO.Database
mstrODBCConnect = "ODBC;Driver={SQL Native Client};" & _
"Server=" & rstRecordSet!ServerName & ";" & _
"Database=" & rstRecordSet!DatabaseName & ";" & _
"UID=" & rstRecordSet!LogInID & _
";PWD=" & rstRecordSet!Password
Set db = CurrentDb
For Each tdf In db.TableDefs
With tdf
' Only process linked ODBC tables
If .Attributes = dbAttachedODBC Then
fLink = LinkODBCTable( _
strLinkName:=.NAME, _
strConnect:=mstrODBCConnect, _
strSourceTableName:=.SourceTableName)
End If
End With
Next tdf
rstRecordSet.MoveNext
Loop
End Sub
Public Function LinkODBCTable(strLinkName As String, strConnect As String, strSourceTableName As String) As Boolean
' Links or relinks a single table.
' Returns True or False based on Err value.
Dim db As DAO.Database
Dim tdf As DAO.TableDef
On Error Resume Next
Set db = CurrentDb
' Check to see if the table link already exists;
' if so, delete it
Set tdf = db.TableDefs(strLinkName)
If Err.Number = 0 Then
db.TableDefs.Delete strLinkName
db.TableDefs.Refresh
Else
' Ignore error and reset
Err.Number = 0
End If
Set tdf = db.CreateTableDef(strLinkName)
tdf.Connect = strConnect
tdf.SourceTableName = strSourceTableName
db.TableDefs.Append tdf
LinkODBCTable = (Err = 0)
End Function
04-03-2009 11:35 AM
Jim -
Not sure if this helps, or is too late, but there is a Macro option called TransferDatabase, which imports a table into Access. Naming the Macro AutoExec caused it to start on opening of Access as well. Just an FYI...