Access/VBScript/SQL question - Tilted Forum Project Discussion Community

innovis · 05-19-2005, 07:18 PM

I have a Data Access Page (generated by Access) that displays about 1000 records in a bunch of different text fields (but one entry at a time). I'm trying to make it so that users have to log in to view that information. I have another table that has username and passwords in it, but I don't know how to use vbscript in the Data Access Page to query the table.

Based on what I read on MSDN and some of the functions in the help section, I'm supposed to connect with ADODB.Connection and then run an SQL query. Well, unfortunately it won't recognize ADODB as an object, and the queries never work (probably half because of the connection, half because of everything else). The queries work in SQL view in the query window.

So lets say I have filename: Asset Inventory, Recordset:accessPage, and fields: userName, passWord. How do I embed vbscript into an html page, have it connect to the database, and query for the information. Also, how can I compare it to, say, textbox1.value? Doesn't SQL return pointers to cells, not their values?

05-20-2005, 04:35 AM

In the page, try something along the lines of:

Code:

set oconn=server.CreateObject("adodb.connection")
set recs=server.CreateObject("adodb.recordset")
oconn.ConnectionString="DSN=stp;"
oconn.open
recs.Open "select blah blah etc from blah", oconn

Notice the usage of a DSN which will be defined on the server. You can create a DSN (otherwise known as an ODBC link among other things) by adding a Data Source (accessable from Control Panel/Administrative Tools in Windows)

The DSN would deal with where the file is located etc, and acts as a window into the database. After it's set up correctly, you can forget the database, and just access the DSN.

One thing to note is that the script will obviously have to actually run server-side.

innovis · 05-24-2005, 06:02 PM

Thanks, I got it to work, but without the DNS

05-19-2005, 07:18 PM	#1 (permalink)
innovis Crazy	Access/VBScript/SQL question I have a Data Access Page (generated by Access) that displays about 1000 records in a bunch of different text fields (but one entry at a time). I'm trying to make it so that users have to log in to view that information. I have another table that has username and passwords in it, but I don't know how to use vbscript in the Data Access Page to query the table. Based on what I read on MSDN and some of the functions in the help section, I'm supposed to connect with ADODB.Connection and then run an SQL query. Well, unfortunately it won't recognize ADODB as an object, and the queries never work (probably half because of the connection, half because of everything else). The queries work in SQL view in the query window. So lets say I have filename: Asset Inventory, Recordset:accessPage, and fields: userName, passWord. How do I embed vbscript into an html page, have it connect to the database, and query for the information. Also, how can I compare it to, say, textbox1.value? Doesn't SQL return pointers to cells, not their values?

05-20-2005, 04:35 AM	#2 (permalink)
zen_tom Guest	In the page, try something along the lines of: Code: set oconn=server.CreateObject("adodb.connection") set recs=server.CreateObject("adodb.recordset") oconn.ConnectionString="DSN=stp;" oconn.open recs.Open "select blah blah etc from blah", oconn Notice the usage of a DSN which will be defined on the server. You can create a DSN (otherwise known as an ODBC link among other things) by adding a Data Source (accessable from Control Panel/Administrative Tools in Windows) The DSN would deal with where the file is located etc, and acts as a window into the database. After it's set up correctly, you can forget the database, and just access the DSN. One thing to note is that the script will obviously have to actually run server-side.

05-24-2005, 06:02 PM	#3 (permalink)
innovis Crazy	Thanks, I got it to work, but without the DNS