DSN less link to SQL

  • Thread starter Thread starter Ngan Bui
  • Start date Start date
N

Ngan Bui

Ok,

I've done this before, but unfortunately, I can't find the
code to do it. Can someone help me?

I have Office XP and SQL 2000. I've upsized the Access
tables to SQL. In a new front end .mdb file, I used the
file/system DSN to link the SQL tables. However, it would
only work on my machine since I have the DSN file.

And I don't want to put the DSN file on every machine that
uses this front end.

How can I turn the link into something that every machine
can use, without having a DSN file? I've seen alot of
codes for dsn-less connection, but I can't seem to get it
to work. I've tried using the code (page 189) listed in
the MS Access Developer's Guide To SQL server. I've tried
using the code at:
http://members.rogers.com/douglas.j.steele/DSNLessLinks.htm
l

However, nothing changes/works.

Anyone can help me?

Ngan
 
Are you getting any error messages when you use my code?

Lots of people have used it, so I doubt that the code is the culprit.
 
Ok, I have a test user part of the ADF group and I added
the ADF group to the SQL login with Public checked.

When I open the mdb file with the SQL linked tables (I ran
your code with no errors) and try to open one of the
tables, I get the following error:

ODBC - call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT
permission denied on object 'tblComplaintType',
database 'ComplaintsdataSQL', owner 'dbo'. (#229).

Did I miss a permission? Do I have to check to see if the
user's computer have a certain driver?

Ngan
 
It sounds like a permissions issue, as opposed to any driver being missing
from the user's machine.

Unfortunately, I don't have SQL Server available to me at the moment, so I
can't suggest what you need to check.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Ngan Bui said:
Ok, I have a test user part of the ADF group and I added
the ADF group to the SQL login with Public checked.

When I open the mdb file with the SQL linked tables (I ran
your code with no errors) and try to open one of the
tables, I get the following error:

ODBC - call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT
permission denied on object 'tblComplaintType',
database 'ComplaintsdataSQL', owner 'dbo'. (#229).

Did I miss a permission? Do I have to check to see if the
user's computer have a certain driver?

Ngan
-----Original Message-----
Are you getting any error messages when you use my code?

Lots of people have used it, so I doubt that the code is the culprit.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)






.
Click to expand...
Click to expand...
 
Just use SQL Server tools to grant permission:

grant select on tblComplaintType to YourUser

--
Joe Fallon
Access MVP



Ngan Bui said:
Ok, I have a test user part of the ADF group and I added
the ADF group to the SQL login with Public checked.

When I open the mdb file with the SQL linked tables (I ran
your code with no errors) and try to open one of the
tables, I get the following error:

ODBC - call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT
permission denied on object 'tblComplaintType',
database 'ComplaintsdataSQL', owner 'dbo'. (#229).

Did I miss a permission? Do I have to check to see if the
user's computer have a certain driver?

Ngan
-----Original Message-----
Are you getting any error messages when you use my code?

Lots of people have used it, so I doubt that the code is the culprit.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)






.
Click to expand...
Click to expand...
 
Ok, I granted the user all rights to the sql db.

When the user tried to view the link table, they got an
error. When I logged onto their machine, I also get the
error. It seems the linked tables can only work on my
machine (that created the links). The error is:

Connection Failed
SQLState: '01000'
SQL Server Error: 53
[Microsoft][ODBC SQL Server Driver][Named Pipes]
ConnectionOpen(CreateFile()).

Connection Failed
SQLState: '08001'
SQL Server Error: 6
[Microsoft][ODBC SQL Server Driver][Named Pipes]Specified
SQL Server not found.

After I clicked OK, it pops up a dialog box asking to
choose the server and the Trusted connection check box.

Ngan

-----Original Message-----
It sounds like a permissions issue, as opposed to any driver being missing
from the user's machine.

Unfortunately, I don't have SQL Server available to me at the moment, so I
can't suggest what you need to check.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Ok, I have a test user part of the ADF group and I added
the ADF group to the SQL login with Public checked.

When I open the mdb file with the SQL linked tables (I ran
your code with no errors) and try to open one of the
tables, I get the following error:

ODBC - call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT
permission denied on object 'tblComplaintType',
database 'ComplaintsdataSQL', owner 'dbo'. (#229).

Did I miss a permission? Do I have to check to see if the
user's computer have a certain driver?

Ngan
-----Original Message-----
Are you getting any error messages when you use my code?

Lots of people have used it, so I doubt that the code
Click to expand...
is
the culprit.
--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



"Ngan Bui" <[email protected]> wrote
Click to expand...
in
message
Ok,

I've done this before, but unfortunately, I can't
Click to expand...
find
the
code to do it. Can someone help me?

I have Office XP and SQL 2000. I've upsized the Access
tables to SQL. In a new front end .mdb file, I used the
file/system DSN to link the SQL tables. However, it would
only work on my machine since I have the DSN file.

And I don't want to put the DSN file on every machine that
uses this front end.

How can I turn the link into something that every machine
can use, without having a DSN file? I've seen alot of
codes for dsn-less connection, but I can't seem to
Click to expand...
get
it
to work. I've tried using the code (page 189) listed in
the MS Access Developer's Guide To SQL server. I've tried
using the code at:
Click to expand...
http://members.rogers.com/douglas.j.steele/DSNLessLinks.htm
l

However, nothing changes/works.

Anyone can help me?

Ngan


.
Click to expand...
Click to expand...


.
Click to expand...
 
Are you sure that MDAC was installed properly, so that the necessary SQL
Server drivers exist on their machine?

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Ngan Bui said:
Ok, I granted the user all rights to the sql db.

When the user tried to view the link table, they got an
error. When I logged onto their machine, I also get the
error. It seems the linked tables can only work on my
machine (that created the links). The error is:

Connection Failed
SQLState: '01000'
SQL Server Error: 53
[Microsoft][ODBC SQL Server Driver][Named Pipes]
ConnectionOpen(CreateFile()).

Connection Failed
SQLState: '08001'
SQL Server Error: 6
[Microsoft][ODBC SQL Server Driver][Named Pipes]Specified
SQL Server not found.

After I clicked OK, it pops up a dialog box asking to
choose the server and the Trusted connection check box.

Ngan

-----Original Message-----
It sounds like a permissions issue, as opposed to any driver being missing
from the user's machine.

Unfortunately, I don't have SQL Server available to me at the moment, so I
can't suggest what you need to check.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Ok, I have a test user part of the ADF group and I added
the ADF group to the SQL login with Public checked.

When I open the mdb file with the SQL linked tables (I ran
your code with no errors) and try to open one of the
tables, I get the following error:

ODBC - call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT
permission denied on object 'tblComplaintType',
database 'ComplaintsdataSQL', owner 'dbo'. (#229).

Did I miss a permission? Do I have to check to see if the
user's computer have a certain driver?

Ngan
-----Original Message-----
Are you getting any error messages when you use my code?

Lots of people have used it, so I doubt that the code is
the culprit.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



message
Ok,

I've done this before, but unfortunately, I can't find
the
code to do it. Can someone help me?

I have Office XP and SQL 2000. I've upsized the Access
tables to SQL. In a new front end .mdb file, I used the
file/system DSN to link the SQL tables. However, it
would
only work on my machine since I have the DSN file.

And I don't want to put the DSN file on every machine
that
uses this front end.

How can I turn the link into something that every
machine
can use, without having a DSN file? I've seen alot of
codes for dsn-less connection, but I can't seem to get
it
to work. I've tried using the code (page 189) listed in
the MS Access Developer's Guide To SQL server. I've
tried
using the code at:

http://members.rogers.com/douglas.j.steele/DSNLessLinks.htm
l

However, nothing changes/works.

Anyone can help me?

Ngan


.
Click to expand...


.
Click to expand...
Click to expand...
 
Man...I am stupid. I did check the MDAC and installed
2.7...and it worked :(

Sorry for the question. I fergot about the MDAC thing.

One more thing, when i do the link, the table name starts
with dbo_tblComplaints.

Is there a way to programatically change it back to
tblComplaints?
-----Original Message-----
Are you sure that MDAC was installed properly, so that the necessary SQL
Server drivers exist on their machine?

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Ok, I granted the user all rights to the sql db.

When the user tried to view the link table, they got an
error. When I logged onto their machine, I also get the
error. It seems the linked tables can only work on my
machine (that created the links). The error is:

Connection Failed
SQLState: '01000'
SQL Server Error: 53
[Microsoft][ODBC SQL Server Driver][Named Pipes]
ConnectionOpen(CreateFile()).

Connection Failed
SQLState: '08001'
SQL Server Error: 6
[Microsoft][ODBC SQL Server Driver][Named Pipes] Specified
SQL Server not found.

After I clicked OK, it pops up a dialog box asking to
choose the server and the Trusted connection check box.

Ngan

-----Original Message-----
It sounds like a permissions issue, as opposed to any driver being missing
from the user's machine.

Unfortunately, I don't have SQL Server available to me
Click to expand...
at
the moment, so I
can't suggest what you need to check.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



"Ngan Bui" <[email protected]> wrote
Click to expand...
in
message
Ok, I have a test user part of the ADF group and I added
the ADF group to the SQL login with Public checked.

When I open the mdb file with the SQL linked tables
Click to expand...
(I
ran
your code with no errors) and try to open one of the
tables, I get the following error:

ODBC - call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT
permission denied on object 'tblComplaintType',
database 'ComplaintsdataSQL', owner 'dbo'. (#229).

Did I miss a permission? Do I have to check to see
Click to expand...
if
the
user's computer have a certain driver?

Ngan
-----Original Message-----
Are you getting any error messages when you use my code?

Lots of people have used it, so I doubt that the
Click to expand...
code
is
the culprit.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



"Ngan Bui" <[email protected]>
Click to expand...
wrote
in
message
Ok,

I've done this before, but unfortunately, I can't find
the
code to do it. Can someone help me?

I have Office XP and SQL 2000. I've upsized the Access
tables to SQL. In a new front end .mdb file, I
Click to expand...
used
the
file/system DSN to link the SQL tables. However, it
would
only work on my machine since I have the DSN file.

And I don't want to put the DSN file on every machine
that
uses this front end.

How can I turn the link into something that every
machine
can use, without having a DSN file? I've seen
Click to expand...
alot
of
codes for dsn-less connection, but I can't seem to get
it
to work. I've tried using the code (page 189)
Click to expand...
listed
in
the MS Access Developer's Guide To SQL server. I've
tried
using the code at:
Click to expand...
http://members.rogers.com/douglas.j.steele/DSNLessLinks.htm
l

However, nothing changes/works.

Anyone can help me?

Ngan


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
This eliminates the need to re-name all the tables to strip out dbo_ and it
allows you to point to different versions of the same database easily.
====================================================

I use this procedure to re-create links to SQL Server.
====================================================
For Jet re-linking code see:
http://www.mvps.org/access/tables/tbl0009.htm
====================================================
There is a local Access table (tblODBCTables) that contains the table names
I want to link to on the Server.
Note: the source table name needs the dbo. prefix which is in the code. The
linked table name usualy omits this. .

Public Sub LinkSQLServerTables(strDSN As String, strDatabase)
On Error GoTo Err_LinkSQLServerTables

Dim dbs As Database, rs As Recordset, tdfAccess As TableDef
Dim dbsODBC As Database, strConnect As String

If strDSN = "" Then
MsgBox "You must supply a DSN in order to link tables."
Exit Sub
Else
strConnect = "ODBC;DSN=" & strDSN & ";UID=User;PWD=password;DATABASE=" &
strDatabase & ";"
End If

SysCmd acSysCmdSetStatus, "Connecting to SQL Server..."

Call DeleteODBCTableNames

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tblODBCTables")
Set dbsODBC = OpenDatabase("", False, False, strConnect)

Do While Not rs.EOF
Set tdfAccess = dbs.CreateTableDef(rs![LinkTablename], dbAttachSavePWD)
tdfAccess.Connect = dbsODBC.Connect
tdfAccess.SourceTableName = dbsODBC.TableDefs("dbo." &
rs![LinkTablename]).Name
dbs.TableDefs.Append tdfAccess
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set dbsODBC = Nothing
Set dbs = Nothing

Exit_LinkSQLServerTables:
SysCmd acSysCmdClearStatus
Exit Sub

Err_LinkSQLServerTables:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_LinkSQLServerTables

End Sub


'This procedure deletes all linked ODBC table names in an mdb.
Public Sub DeleteODBCTableNames()
On Error GoTo Err_DeleteODBCTableNames

Dim dbs As Database, tdf As TableDef, I As Integer
Set dbs = CurrentDb
For I = dbs.TableDefs.Count - 1 To 0 Step -1
Set tdf = dbs.TableDefs(I)
If (tdf.Attributes And dbAttachedODBC) Then
dbs.TableDefs.Delete (tdf.Name)
End If
Next I

dbs.Close
Set dbs = Nothing

Exit_DeleteODBCTableNames:
Exit Sub

Err_DeleteODBCTableNames:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_DeleteODBCTableNames

End Sub

--
Joe Fallon
Access MVP



Ngan Bui said:
Man...I am stupid. I did check the MDAC and installed
2.7...and it worked :(

Sorry for the question. I fergot about the MDAC thing.

One more thing, when i do the link, the table name starts
with dbo_tblComplaints.

Is there a way to programatically change it back to
tblComplaints?
-----Original Message-----
Are you sure that MDAC was installed properly, so that the necessary SQL
Server drivers exist on their machine?

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Ok, I granted the user all rights to the sql db.

When the user tried to view the link table, they got an
error. When I logged onto their machine, I also get the
error. It seems the linked tables can only work on my
machine (that created the links). The error is:

Connection Failed
SQLState: '01000'
SQL Server Error: 53
[Microsoft][ODBC SQL Server Driver][Named Pipes]
ConnectionOpen(CreateFile()).

Connection Failed
SQLState: '08001'
SQL Server Error: 6
[Microsoft][ODBC SQL Server Driver][Named Pipes] Specified
SQL Server not found.

After I clicked OK, it pops up a dialog box asking to
choose the server and the Trusted connection check box.

Ngan


-----Original Message-----
It sounds like a permissions issue, as opposed to any
driver being missing
from the user's machine.

Unfortunately, I don't have SQL Server available to me at
the moment, so I
can't suggest what you need to check.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



message
Ok, I have a test user part of the ADF group and I added
the ADF group to the SQL login with Public checked.

When I open the mdb file with the SQL linked tables (I
ran
your code with no errors) and try to open one of the
tables, I get the following error:

ODBC - call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT
permission denied on object 'tblComplaintType',
database 'ComplaintsdataSQL', owner 'dbo'. (#229).

Did I miss a permission? Do I have to check to see if
the
user's computer have a certain driver?

Ngan
-----Original Message-----
Are you getting any error messages when you use my
code?

Lots of people have used it, so I doubt that the code
is
the culprit.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



in
message
Ok,

I've done this before, but unfortunately, I can't
find
the
code to do it. Can someone help me?

I have Office XP and SQL 2000. I've upsized the
Access
tables to SQL. In a new front end .mdb file, I used
the
file/system DSN to link the SQL tables. However, it
would
only work on my machine since I have the DSN file.

And I don't want to put the DSN file on every machine
that
uses this front end.

How can I turn the link into something that every
machine
can use, without having a DSN file? I've seen alot
of
codes for dsn-less connection, but I can't seem to
get
it
to work. I've tried using the code (page 189) listed
in
the MS Access Developer's Guide To SQL server. I've
tried
using the code at:


http://members.rogers.com/douglas.j.steele/DSNLessLinks.htm
l

However, nothing changes/works.

Anyone can help me?

Ngan


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top