connection between front-ends

[tomvdduin]

Hi all!

I want to make a versionmanager that will automatically copy the newest version of my frontend on the server to the workstation.

I'm using the access security by using a custom .MDW file, with a disgranted admin group in it.

In the frontend I have a local versiontable, in with I want to put a version number. When the versionnumber in the frontend on the server higher is then the versionnumber in the local frontend, the frontend is copied and locally opened.

The problem is the following:
- When I want to open one of the frontends using an adodb connection, I am granted the access, but I can't open the table with the version number with the following error: -2147217911, cannot select. I don't have privileges to read the table.

How can I force Access to use the login I entered when I opened the frontend?

This is my (test) code:

Public Function testmgr()

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

Dim lv_filename_local As String
Dim lv_filename_server As String

Dim lv_version_local As String
Dim lv_version_server As String

    Set cnn = CurrentProject.Connection

    rst.Open "Select * from tVersie", cnn, adOpenKeyset, adLockReadOnly, adCmdTableDirect

    lv_filename_local= rst!ver_locatie_lokaal & rst!ver_filenaam
    lv_filename_server = rst!ver_locatie_server & rst!ver_filenaam
    rst.Close
    cnn.Close
    
    With cnn
       .Provider = "Microsoft.Jet.OLEDB.4.0"
       .Properties("Data Source") = lv_filename_local
       .Open
    End With

    rst.Open "SELECT max(versie) as versie from tbl_ALG_versie", cnn, adOpenKeyset, adLockReadOnly, adCmdTableDirect
    lv_version_local = rst!versie

End Function

as you can see the code is not yet finished, but maybe you can help me? the rst. statement gives the error.

Thanks!

Tom

 

[Zion7]

which rst statement? What's the error?


lv_version_local is a string, is versie text data?
Why the Max function on text data? Will it retrieve records starting with "z"? (i'm not being facetious)

 

[DaOtH]

Tom,

Check this thread and keep an eye on it, will be putting down some little program in a couple of day's (i hope)

"In three words I can sum up everything I've learned about life: it goes on."
- Robert Frost 1874-1963

 

[tomvdduin]

@Zion7:i'm sorry for the confusion, I ment the second rst: rst.open "SELECT max(versie)..."

I select a max, because in that table I put records with all the new additions to the system. It's a string, because it contains digits and letters.

The whole idea is to compare the highest version number in the local frontend with the highest version number in the frontend on the server.

I don't have any Lasy Users, I've raised them good... They all shut down their workstations!

@DaOtH: That thread looks interesting... I'll watch it! But it's not quite as I want it: I want to lookup version numbers from within a MDB/MDE file, but maybe the .exe file also does the job.

Tom

 

[cmmrfrds]

Check out connection strings at

http://www.carlprothman.net/Default.aspx?tabid=81

There are examples using the system.mdw file in the connection string.

 

[tomvdduin]

cmmrfrds:
That's a very handy site! I knew

http://www.connectionstrings.com,

but this site has much more info.
On the site, I found the following info:

If using a Workgroup (System Database)

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\somepath\mydb.mdb;" & _
"Jet OLEDB:System Database=MySystem.mdw", _
"myUsername", "myPassword"

My problem now is: I want to connect to a remote access file, with the "myUsername" and "myPassword" I used to login to the versionmanager... I can substitute "MyUsername" with CurrentUser, but I don't know how to get the "MyPassword"

btw, I'm using the same mdw file on the versionmanager and the front-end

Tom

 

[PHV]

Why not simply using a linked table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cmmrfrds]

Tom, the easiest way to test out your connection string is with the udl wizard. To invoke the udl wizard.

1. create a blank .txt file in notepad.
2. save the empty file.
3. rename the empty file with the extention .udl
4. the file will change to an icon that indicates the wizard.
5. open the file and select the Jet 4.0 provider and follow through setting up your connection.
6. test the connection using the test button.
7. close the file.
8. open the file with notepad and you will see the generated connection string. use this string.