Community
Showing results for 
Search instead for 
Do you mean 
Reply

Actreader programmatically?

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Actreader programmatically?

I we want to write code to read the database via Actreader, is it possible to set the password programattically rather than having to do in manually on each system?
Platinum Elite Contributor
Posts: 14,384
Country: Australia
New Member
Posts: 11
Country: United States

Re: Actreader programmatically?

Mike,

 

How did you solve this issue?

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: Actreader programmatically?

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?

New Member
Posts: 11
Country: United States

Re: Actreader programmatically?

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

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: Actreader programmatically?

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!?

New Member
Posts: 11
Country: United States

Re: Actreader programmatically?

 

ACT 11.1 - I will see if I can find a setting in ACT to make the Id and Psw persistent.

 

Jim

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: Actreader programmatically?

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

New Member
Posts: 11
Country: United States

Re: Actreader programmatically?

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

Nickel Elite Contributor
Posts: 508
Country: USA

Re: Actreader programmatically?

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...

Richard Brust
ACT! Certified Consultant
richard@rbrDataSolutions.com