Problem on quering Access link table

[tabonni]

Hi All

I connected MS Exchange Server to MS Access database using Access link
table function. I can open the database table and see the fields and
data inside the link table. However, the table seems cannot be queried
by SQL statement.

My situation is:
I'm building an intranet. I have a ASP login page for all staff in the
company to login. Other people can't register or login the intranet.
That's the reason I use the company email address to verify username
login name.

After I made the link to the Global Address List (rename to
StaffList), I can see the Alias field. I wrote ASP code to use Alias
field to verify user login name. But, the ASP page seems experiencing
difficulties. I always got the "Connection Timeout" Error.

The procedure I connect Exchange to Access:
[New Table -> Link Table -> File of Type [I choose Exchange()] ->
Choose "Global Adress List" -> Rename to "StaffList -> Finish]

My ASP code is as follow:

<%@ Language=VBScript %>
<!-- #INCLUDE VIRTUAL="/intranet/connection.asp" -->
<% Response.Buffer = true %>
<%

Dim RecordSet, strSQL, strUsername

strUsername = Request.Form("Username")

strSQL = "SELECT Alias FROM StaffList"

Set RecordSet = Server.CreateObject("ADODB.Recordset")
RecordSet.Open strSQL, databaseConnection

Do Until RecordSet.EOF
If(StrComp(RecordSet("Alias"), strUsername) = 0) Then
Response.Write ("You are staff")
Else
RecordSet.MoveNext
Loop

RecordSet.Close
Set RecordSet = Nothing
databaseConnection.Close
Set databaseConnection = Nothing
%>

The ASP code for connection.asp:
<%
Dim databaseConnection
Set databaseConnection = Server.CreateObject("ADODB.Connection")
databaseConnection.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("/intranet/IntranetDB.mdb") & ";"
databaseConnection.Open
%>

Please tell me what do you observe? I'm really stuck.

Thank you.

 

[Roger Carlson]

My ASP is a little rusty, but two things strike me. One is a potential
problem. The other is stylistic.

1) I'm bothered by the fieldname "Alias". It is possible that it's a
reserved word. I'd name it something else.

2) As I said, more of a style difference. Why create a recordset of all
users and then loop through it? Why not add a WHERE clause to the SQL and
simply test the recordset for BOF/EOF?

Dim RecordSet, strSQL, strUsername

strUsername = Request.Form("Username")

strSQL = "SELECT Alias FROM StaffList WHERE Alias = '" & strUserName & "'"

Set RecordSet = Server.CreateObject("ADODB.Recordset")
RecordSet.Open strSQL, databaseConnection

If Not RecordSet.EOF then
Response.Write ("You are staff")
End If

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hi All

I connected MS Exchange Server to MS Access database using Access link
table function. I can open the database table and see the fields and
data inside the link table. However, the table seems cannot be queried
by SQL statement.

My situation is:
I'm building an intranet. I have a ASP login page for all staff in the
company to login. Other people can't register or login the intranet.
That's the reason I use the company email address to verify username
login name.

After I made the link to the Global Address List (rename to
StaffList), I can see the Alias field. I wrote ASP code to use Alias
field to verify user login name. But, the ASP page seems experiencing
difficulties. I always got the "Connection Timeout" Error.

The procedure I connect Exchange to Access:
[New Table -> Link Table -> File of Type [I choose Exchange()] ->
Choose "Global Adress List" -> Rename to "StaffList -> Finish]

My ASP code is as follow:

<%@ Language=VBScript %>
<!-- #INCLUDE VIRTUAL="/intranet/connection.asp" -->
<% Response.Buffer = true %>
<%

Dim RecordSet, strSQL, strUsername

strUsername = Request.Form("Username")

strSQL = "SELECT Alias FROM StaffList"

Set RecordSet = Server.CreateObject("ADODB.Recordset")
RecordSet.Open strSQL, databaseConnection

Do Until RecordSet.EOF
If(StrComp(RecordSet("Alias"), strUsername) = 0) Then
Response.Write ("You are staff")
Else
RecordSet.MoveNext
Loop

RecordSet.Close
Set RecordSet = Nothing
databaseConnection.Close
Set databaseConnection = Nothing
%>

The ASP code for connection.asp:
<%
Dim databaseConnection
Set databaseConnection = Server.CreateObject("ADODB.Connection")
databaseConnection.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("/intranet/IntranetDB.mdb") & ";"
databaseConnection.Open
%>

Please tell me what do you observe? I'm really stuck.

Thank you.

 

[[MVP] S.Clark]

If it's timing out, try setting the connection timeout to a higher value.

 

[Roger Carlson]

Shoot! I wasn't thinking!

Is "Alias" a field in the Exchange table? Maybe you need to bracket the
name to make sure it knows it's a field you're talking about:

strSQL = "SELECT [Alias] FROM StaffList WHERE [Alias] = '" & strUserName &
"'"

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

My ASP is a little rusty, but two things strike me. One is a potential
problem. The other is stylistic.

1) I'm bothered by the fieldname "Alias". It is possible that it's a
reserved word. I'd name it something else.

2) As I said, more of a style difference. Why create a recordset of all
users and then loop through it? Why not add a WHERE clause to the SQL and
simply test the recordset for BOF/EOF?

Dim RecordSet, strSQL, strUsername

strUsername = Request.Form("Username")

strSQL = "SELECT Alias FROM StaffList WHERE Alias = '" & strUserName & "'"

Set RecordSet = Server.CreateObject("ADODB.Recordset")
RecordSet.Open strSQL, databaseConnection

If Not RecordSet.EOF then
Response.Write ("You are staff")
End If

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hi All

I connected MS Exchange Server to MS Access database using Access link
table function. I can open the database table and see the fields and
data inside the link table. However, the table seems cannot be queried
by SQL statement.

My situation is:
I'm building an intranet. I have a ASP login page for all staff in the
company to login. Other people can't register or login the intranet.
That's the reason I use the company email address to verify username
login name.

After I made the link to the Global Address List (rename to
StaffList), I can see the Alias field. I wrote ASP code to use Alias
field to verify user login name. But, the ASP page seems experiencing
difficulties. I always got the "Connection Timeout" Error.

The procedure I connect Exchange to Access:
[New Table -> Link Table -> File of Type [I choose Exchange()] ->
Choose "Global Adress List" -> Rename to "StaffList -> Finish]

My ASP code is as follow:

<%@ Language=VBScript %>
<!-- #INCLUDE VIRTUAL="/intranet/connection.asp" -->
<% Response.Buffer = true %>
<%

Dim RecordSet, strSQL, strUsername

strUsername = Request.Form("Username")

strSQL = "SELECT Alias FROM StaffList"

Set RecordSet = Server.CreateObject("ADODB.Recordset")
RecordSet.Open strSQL, databaseConnection

Do Until RecordSet.EOF
If(StrComp(RecordSet("Alias"), strUsername) = 0) Then
Response.Write ("You are staff")
Else
RecordSet.MoveNext
Loop

RecordSet.Close
Set RecordSet = Nothing
databaseConnection.Close
Set databaseConnection = Nothing
%>

The ASP code for connection.asp:
<%
Dim databaseConnection
Set databaseConnection = Server.CreateObject("ADODB.Connection")
databaseConnection.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("/intranet/IntranetDB.mdb") & ";"
databaseConnection.Open
%>

Please tell me what do you observe? I'm really stuck.

Thank you.

 

[tabonni]

Hi All

Yes. Alias is the field name of Exchange table.

I changed my code, but I still can't make it work. Sometimes, I got
"Microsoft JET Database Engine (0x80004005)The Microsoft Jet database
engine has already been initialized.", after I enter the username and
press submit button.

Sometimes,the page took a long time to load and got "Connection
Timeout Error" Eventually.

I changed code as follow:

<%@ Language=VBScript %>
<!-- #INCLUDE VIRTUAL="/intranet/connection.asp" -->
<% Response.Buffer = true %>
<%
Dim RecordSet, strUsername,strSQL
strUsername = TRIM(Request.Form("Username"))
strSQL = "SELECT [Alias] FROM StaffList WHERE [Alias]='" & strUsername
& "'"
Set RecordSet = Server.CreateObject("ADODB.Recordset"
RecordSet.Open strSQL, databaseConnection
If NOT RecordSet.EOF Then
Response.Write "You are staff"
Else
Response.Write "You are NOT staff"
End If

RecordSet.Close
Set RecordSet = Nothing
databaseConnection.Close
Set databaseConnection = Nothing
%>

I'm wondering it is something wrong on the procedure when I connect
the MS Exchange to MS Access.
[New Table -> Link Table -> File of Type [I choose Exchange()] ->
Choose "Global Adress List" -> Rename to "StaffList -> Finish].

I tried to choose Exchange() and Outlook(). But, I still can't make it
work. BTW, what's the difference between Exchange() and Outlook()?

Thank you.

If it's timing out, try setting the connection timeout to a higher value.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

Hi All

I connected MS Exchange Server to MS Access database using Access link
table function. I can open the database table and see the fields and
data inside the link table. However, the table seems cannot be queried
by SQL statement.

My situation is:
I'm building an intranet. I have a ASP login page for all staff in the
company to login. Other people can't register or login the intranet.
That's the reason I use the company email address to verify username
login name.

After I made the link to the Global Address List (rename to
StaffList), I can see the Alias field. I wrote ASP code to use Alias
field to verify user login name. But, the ASP page seems experiencing
difficulties. I always got the "Connection Timeout" Error.

The procedure I connect Exchange to Access:
[New Table -> Link Table -> File of Type [I choose Exchange()] ->
Choose "Global Adress List" -> Rename to "StaffList -> Finish]

My ASP code is as follow:

<%@ Language=VBScript %>
<!-- #INCLUDE VIRTUAL="/intranet/connection.asp" -->
<% Response.Buffer = true %>
<%

Dim RecordSet, strSQL, strUsername

strUsername = Request.Form("Username")

strSQL = "SELECT Alias FROM StaffList"

Set RecordSet = Server.CreateObject("ADODB.Recordset")
RecordSet.Open strSQL, databaseConnection

Do Until RecordSet.EOF
If(StrComp(RecordSet("Alias"), strUsername) = 0) Then
Response.Write ("You are staff")
Else
RecordSet.MoveNext
Loop

RecordSet.Close
Set RecordSet = Nothing
databaseConnection.Close
Set databaseConnection = Nothing
%>

The ASP code for connection.asp:
<%
Dim databaseConnection
Set databaseConnection = Server.CreateObject("ADODB.Connection")
databaseConnection.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("/intranet/IntranetDB.mdb") & ";"
databaseConnection.Open
%>

Please tell me what do you observe? I'm really stuck.

Thank you.