Hello, John,
Access has a place where you can create queries. Click
on the Query button. What pops up is a list of queries
and tables within the Access .mdb/.mde file. You can
select multiple tables there to create a query. You can
link two tables together if needed.
That is really nice, because you can see the query before
you use it in code. And what else is nice, is that after the
query is created and saved, you can right click on it and
click on the View SQL option. It will display the SQL
code that can be created.
I believe Jens Peter Karlsen is wrong in stating that you
MUST use SQL. I believe you can connect to the query
and use it as a recordset without any SQL. At least that's
the way I do it with DAO. DAO is a little different than
ADO, but I think they connect to recordsets and queries
in pretty much the same manner.
Dim oadodb As New ADODB.Connection
Dim oadors As New ADODB.Recordset
Dim sDBPath As String
sDBPath = Server.MapPath("db.mdb")
Set oadodb = ADODB.OpenConnection(sDBPath)
Set oadors = oadodb.OpenRecordset("qSelectCustomerInvoice")
It'll be very close to such and my syntax might be a little
ajar...
For IIS, it would be similar to:
Dim sDBPath, adoCon, sCon, oAdoRS, sRS
sDbPath = "/fpdb/data1.mdb"
Set adoCon = Server.CreateObject("ADODB.Connection")
sCon = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath(sDbPath)
adoCon.Open sCon 'open connection
'open recordset
'Set oAdoRS = Server.CreateObject("ADODB.Command")
Set oAdoRS = Server.CreateObject("ADODB.Recordset")
'oAdoRS.CommandType = adCmdStoredProc 'it's a stored procedure
sRS = "qSelectCustomerInvoice"
oAdoRS.Open sRS, sCon, adOpenForwardOnly, adLockReadOnly, adCmdTable
I haven't tested the code above. I pulled some of it from a
project and changed the sSQL to sRS. The sSQL was a
"Select ..." query. But I know that the same thing can be
accomplished with an ADODB.Command object. And I
think it can be accomplished via the Recordset object as
well. The Command object syntax that is commented out
above was pulled from ADODB 2.8 SDK Reference.
It wouldn't take all that much effort to test it out.
Hope that helps.
--
Jim Carlock
Post replies to newsgroup.
Hi all,
I am fairly familiar with using ASP to open an Access database table.
What I need to know is whether it's possible to open multiple tables
at the same time so to read or change the contents of the tables from
one asp page.
If possible I don't want to use SQL (no knowledge of it yet)
Any ideas anyone?
TIA.
