reprosser
asked on
How to link A2K tables using UDL or DNS-less (what code and where)
I have an A2K front end database that I want to link to SQL Server 7 backend. I would like to distribute the Access database to multiple users. Each user would open the Access database and have ability to update data through the front end. I have created user ("tester") on SQL database, but would like all Access users to be connected to SQL as "tester" without seeing login screen. This is NOT a web based implementation.
I have seen a UDL file with login and password data that would appear to eliminate the login screen, but don't know how to implement.
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Passwo
Is this the correct method, or is there a better way ? I would like NOT to have to include a special file along with the Access database to the user. (but it may be required)
User OS will be Windows2000
If the method involves adding code - where should the code be placed, and how does it get executed ?
I have seen a UDL file with login and password data that would appear to eliminate the login screen, but don't know how to implement.
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Passwo
Is this the correct method, or is there a better way ? I would like NOT to have to include a special file along with the Access database to the user. (but it may be required)
User OS will be Windows2000
If the method involves adding code - where should the code be placed, and how does it get executed ?
ASKER
Right now I am using a DSN to connect, and I am having problems (tries to connect as the domain login instead of SQL login).
Users will open and close the Access FE multiple times during the day, so I think I need reconnection at startup.
Where do I put the connection string information ? I use the autoexec macro to bring up the first form when the database is opened.
Users will open and close the Access FE multiple times during the day, so I think I need reconnection at startup.
Where do I put the connection string information ? I use the autoexec macro to bring up the first form when the database is opened.
I have the code that I can paste in here later...I'm short on time right now...I store a local table that contains a list of tables that need to be reconnected on startup....will post and explain later tonight.
Mike
Mike
ASKER
OK - Great. I have 13 linked tables.
create a table called tblLinkedTables....this table is local....it is in the mdb.
Two fields...
TableName text 50 Primary key
Type text 50
Example of data
tblEmployees ODBC
tblVendors ODBC
The ODBC is a little misleading....I use it as a flag to tell the code that is comes from SQL Server.
The great thing about this solution is that if you add more tables to SQL Server you need only add the tablename to tblLinkedTables and the next time you open the database, the table is there.
I'll have the code in a little while....I'm on the East Coast of the US....Eastern Standard Time
Mike
Two fields...
TableName text 50 Primary key
Type text 50
Example of data
tblEmployees ODBC
tblVendors ODBC
The ODBC is a little misleading....I use it as a flag to tell the code that is comes from SQL Server.
The great thing about this solution is that if you add more tables to SQL Server you need only add the tablename to tblLinkedTables and the next time you open the database, the table is there.
I'll have the code in a little while....I'm on the East Coast of the US....Eastern Standard Time
Mike
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. I created the table and copied the code into a new module.
I get a user defined type error when I try to run the module
Private Function MTS_dbLinkTempTableList() As Boolean
On Error GoTo errHandler
Dim rstTemp As adodb.Recordset <--------
I think I have to do something related to ADO - not sure what has to be changed for ADO.
I get a user defined type error when I try to run the module
Private Function MTS_dbLinkTempTableList() As Boolean
On Error GoTo errHandler
Dim rstTemp As adodb.Recordset <--------
I think I have to do something related to ADO - not sure what has to be changed for ADO.
just set a reference to the Microsoft ActiveX Data Objects library.
In the VB Editor open Tools|References
In the VB Editor open Tools|References
ASKER
I checked the references related to ActiveX. I left the DAO reference checked. Will these references travel with the database when I provide it to other users ?
Now if I run the module manually, the tables link.
How do I get the module to run automatically when the database is opened ? There is an AutoExec in the module, but I don't seem to be able to run it from the autoexec macro.
Now that the tables link, the forms load - but I cannot update the data. Will this require ADO changes ?
Now if I run the module manually, the tables link.
How do I get the module to run automatically when the database is opened ? There is an AutoExec in the module, but I don't seem to be able to run it from the autoexec macro.
Now that the tables link, the forms load - but I cannot update the data. Will this require ADO changes ?
Yes, the references go where the database goes...the users will have to have ADO installed on their machines.
Create a macro called AutoExec
action - runcode...put the name of the procedure in the argument dbInit()...we should probably chang the autoexec name to dbInit
If you open the database window and a table, can you edit the table, add new records, etc?
If not, then add this procedure to the module and call this proc at the end of the relink.
Public Function MTS_Relink() As Boolean
'Relink the ODBC Tables linked via ADO
'The white paper (Q276035), said to refresh the link after the append, which I did
'in function MTS_dbLinkTempTablessa in this module. This worked great until
'I added a password to my user name (Access Security). See comments in that procedure
'for details. I'm using DAO to refresh the link. I couldn't find an equivelent method
'in ADO.
Dim tblTemp As TableDef
For Each tblTemp In CurrentDb.TableDefs
If Left(tblTemp.Connect, 4) = "ODBC" Then
tblTemp.RefreshLink
End If
Next
End Function
Hope this solves your problem....my database has 60,000 lines of code.
Mike
Create a macro called AutoExec
action - runcode...put the name of the procedure in the argument dbInit()...we should probably chang the autoexec name to dbInit
If you open the database window and a table, can you edit the table, add new records, etc?
If not, then add this procedure to the module and call this proc at the end of the relink.
Public Function MTS_Relink() As Boolean
'Relink the ODBC Tables linked via ADO
'The white paper (Q276035), said to refresh the link after the append, which I did
'in function MTS_dbLinkTempTablessa in this module. This worked great until
'I added a password to my user name (Access Security). See comments in that procedure
'for details. I'm using DAO to refresh the link. I couldn't find an equivelent method
'in ADO.
Dim tblTemp As TableDef
For Each tblTemp In CurrentDb.TableDefs
If Left(tblTemp.Connect, 4) = "ODBC" Then
tblTemp.RefreshLink
End If
Next
End Function
Hope this solves your problem....my database has 60,000 lines of code.
Mike
Did this solve your problem? if you don't want ADO in your app, then we could probably rewrite the part that uses ADO to use DAO instead.
Mike
Mike
ASKER
Wow - so much info. Thanks. I increased and awarded the points. I will incorporate your last suggestions and post the results.
If users need to get ado - what file should I send them ?
If users need to get ado - what file should I send them ?
don't send them a file...have them install mdac from the Microsoft website....do a search for mdac.
Thanks for the points....There is so much to learn about Access and VBA...it can be great fun!!!!
Mike
Thanks for the points....There is so much to learn about Access and VBA...it can be great fun!!!!
Mike
ASKER
I must have syntax wrong - I can't seem to get the autoexec macro to see the (renamed) Sub dbInit(). Should it be a Public Function instead ?
In the autoexec macro I have:
RunCode
Function Name: dbInit()
Generates error - "The expression you entered has a function name that Access can't find"
...It can be great fun. Sometimes too much fun ;-)
In the autoexec macro I have:
RunCode
Function Name: dbInit()
Generates error - "The expression you entered has a function name that Access can't find"
...It can be great fun. Sometimes too much fun ;-)
yes it has to be a public procedure
ASKER
I still cannot edit the data. I can open the linked tables, I can view in design mode (no edit as expected), but I cannot change the data in the tables.
I put the relink in MTS_dbLinkTempTablessa here:
'Append the newly identified tblDef to this database
CurrentDb.TableDefs.Append
MTS_dbLinkTempTablessa = True
MTS_Relink
I put the relink in MTS_dbLinkTempTablessa here:
'Append the newly identified tblDef to this database
CurrentDb.TableDefs.Append
MTS_dbLinkTempTablessa = True
MTS_Relink
hmmmm....what about permissions in SQL Server on the table with that user name? Do they have full access?
what versions of software are you using?
Mike
what versions of software are you using?
Mike
ASKER
Using SQL Server 7 and MSAccess 2002 (with MSAccess 2000 database)
Other users will be using MSAccess 2000.
The user permissions have full access on SQL Server.
If I connect thru my workstation which is logged onto the company domain, when I tr to make changes, I get a beeping noise but no message.
If I connect thru a PC that is not on the domain, when I try to make changes, I see a "Recordset is not updateable" message in the status bar.
Is there a "Trusted connection" parameter in the connect string ?
Other users will be using MSAccess 2000.
The user permissions have full access on SQL Server.
If I connect thru my workstation which is logged onto the company domain, when I tr to make changes, I get a beeping noise but no message.
If I connect thru a PC that is not on the domain, when I try to make changes, I see a "Recordset is not updateable" message in the status bar.
Is there a "Trusted connection" parameter in the connect string ?
Try out this website...it has connection strings to just about everything.
http://www.connectionstrings.com/
http://www.connectionstrings.com/
What OS are you using?
ASKER
I just noticed that none of the linked tables show a primary key. Not sure if this is normal for SQL Server linked tables.
ASKER
OS is Win2000 Pro
With a primary key, the data is not updateable....check your indexes in SQL Server.
Mike
Mike
what data type are some of the Primary keys in SQL Server...do you have clustered indexes on any of the tables?
Mike
Mike
ASKER
If I go back to the DSN connection to SQL Server, I can log in as tester and update the fields. I can also see the primary key in the tables.
This indicates to me that the permissions are set correctly on SQL, and the database has primary keys on SQL.
Must be something in the ADO connection method that blocks the primary key or something ?
This indicates to me that the permissions are set correctly on SQL, and the database has primary keys on SQL.
Must be something in the ADO connection method that blocks the primary key or something ?
I use the same connection string that I sent you except that I'm using trustedconnection = SSPI
If there are unique constraints, clustered indexes and primary keys, Access doen't know which one to use...I've seen it come across to Access and not have the primary key or have it set to the wrong field.
Mike
If there are unique constraints, clustered indexes and primary keys, Access doen't know which one to use...I've seen it come across to Access and not have the primary key or have it set to the wrong field.
Mike
ASKER
Aughrrr!! - it seems that there were no primary keys in the SQL tables. I am not familiar with SQL Server, but I know when the tables were converted, SQL asked for primary keys - and they were indicated. Must not have actually gotten into the file for some reason. Don't know why they show up when a DSN connect is used...
I added keys, and for now - it all seems to be working. I can open the Access FE and it links to the SQL Server BE and I can edit the data. Now if I can just get the users and roles set up correctly ;-)
Sorry it took so long to nail it down, but thanks Mike for all the help and suggestions.
An A++ perfomance.
rick
I added keys, and for now - it all seems to be working. I can open the Access FE and it links to the SQL Server BE and I can edit the data. Now if I can just get the users and roles set up correctly ;-)
Sorry it took so long to nail it down, but thanks Mike for all the help and suggestions.
An A++ perfomance.
rick
Rick....glad I could help out a fellow Access developer....good luck with your application.
Regards,
Mike
Regards,
Mike
provider=Microsoft.Access.
I use this connection string via ADO to reconnect all my tables at startup...what I end up with is are DSN-less linked tables.
Do you need to reconnection at startup? If not and your database already is setup for dsn-less, then you should be able to use your database without doing anything else. Unless I'm not understanding what it is you are asking.
Hope this helps
Mike