I am asked to reprogram a Excel/VBA program, and need help
regarding the following code which querying an Access
Database on the server:


Set Conn = CreateObject("ADODB.Connection")
Conn.Open strADOName
strSQL = "SELECT JobNo FROM tblPourItem"
strSQL = strSQL & " WHERE JobNo = '" & strJobNo & "'"


Could someone please direct me to a source where I could
get information relating to querying external DataBase,
like in the above example, what's the function of SELECT?

Especially this line:
strSQL = strSQL & " WHERE JobNo = '" & strJobNo & "'"
WHat is the purpose of ' ?

Also, is there a function in ADO that would let me
retrieve the file's path? Cause I need to find out the
date last modified to Conn(in the example, which is
located on the sever).

This probably isn't the best newsgroup to be asking about SQL.

Since I know a fair bit about SQL, I'll try to answer what I can.
Im getting simple about it. SQL is a huge topic. There are books with 1,000
pages which don't cover everything.

SQL stands for Structured Query Language.
There are 4 ways to work with data: Select, Insert, Update, Delete.

The syntax of a basic Select statement goes a little like:
Select <column name
From <table/view
Where <criteria

As an example, your SQL statement criteria could evaluate to:
JobNo = '12345'

Strings are usually enclosed by single quotes, depending on the database.

Note that most databases are not made up as a single MDB file.
ADO acts as a standard interface to a variety of databases - those which
provide support for OLEDB.

Given it's an Access database, it's quite likely you'll find the file
location as a property of the Connection object. It might even be in the
strADOName variable.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Augustus" wrote in message
...
I am asked to reprogram a Excel/VBA program, and need help
regarding the following code which querying an Access
Database on the server:


Set Conn = CreateObject("ADODB.Connection")
Conn.Open strADOName
strSQL = "SELECT JobNo FROM tblPourItem"
strSQL = strSQL & " WHERE JobNo = '" & strJobNo & "'"


Could someone please direct me to a source where I could
get information relating to querying external DataBase,
like in the above example, what's the function of SELECT?

Especially this line:
strSQL = strSQL & " WHERE JobNo = '" & strJobNo & "'"
WHat is the purpose of ' ?

Also, is there a function in ADO that would let me
retrieve the file's path? Cause I need to find out the
date last modified to Conn(in the example, which is
located on the sever).

Hi Augustus,

Your question is so basic that I wonder if you would be better advised to go
an take an SQL course, read a good boo on SQL, or even employ a programmer
to do it for you, it might save a lot of grief.

On the second point, here is some code to get the last modified date of a
file

Dim FSO As Object
Dim oFile As Object
Dim sfn As String

sfn = InputBox("Supply full file name")
If sfn < "" Then
Set FSO = CreateObject("Scripting.FileSystemObject")
On Error GoTo no_file
Set oFile = FSO.GetFile(sfn)
MsgBox sfn & " " & Format(oFile.DateLAstModified, "dd mmm yyyy")
Exit Sub

no_file:
MsgBox sfn & " cannot be found"
End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Augustus" wrote in message
...
I am asked to reprogram a Excel/VBA program, and need help
regarding the following code which querying an Access
Database on the server:


Set Conn = CreateObject("ADODB.Connection")
Conn.Open strADOName
strSQL = "SELECT JobNo FROM tblPourItem"
strSQL = strSQL & " WHERE JobNo = '" & strJobNo & "'"


Could someone please direct me to a source where I could
get information relating to querying external DataBase,
like in the above example, what's the function of SELECT?

Especially this line:
strSQL = strSQL & " WHERE JobNo = '" & strJobNo & "'"
WHat is the purpose of ' ?

Also, is there a function in ADO that would let me
retrieve the file's path? Cause I need to find out the
date last modified to Conn(in the example, which is
located on the sever).

Thanks, first of all.

I didn't know it is a SQL thing until today (i figure out
lat night that the strSQL is passing a string to
somewhere).

My skill at VBA is very limited. i have done alot of
programming.
Could either of you please tell me how do you know that it
is SQL, and not other form like Jet or ODB?

Please give me a bried explaination on SQL, if you don't
mind?
Thanks
Augustus
-----Original Message-----
I am asked to reprogram a Excel/VBA program, and need
help
regarding the following code which querying an Access
Database on the server:


Set Conn = CreateObject("ADODB.Connection")
Conn.Open strADOName
strSQL = "SELECT JobNo FROM tblPourItem"
strSQL = strSQL & " WHERE JobNo = '" & strJobNo & "'"


Could someone please direct me to a source where I could
get information relating to querying external DataBase,
like in the above example, what's the function of SELECT?

Especially this line:
strSQL = strSQL & " WHERE JobNo = '" & strJobNo & "'"
WHat is the purpose of ' ?

Also, is there a function in ADO that would let me
retrieve the file's path? Cause I need to find out the
date last modified to Conn(in the example, which is
located on the sever).
.

Augustus,

There are a number of bits that you did not give us.

Set Conn = CreateObject("ADODB.Connection")

This simply creates an ADO connection object.

Conn.Open strADOName

This is where the connection is opened. As you can see, it uses a variable
strADOName, which is what are called the connection parameters, (server,
data source, id, password, etc). Somewhere in your code this muse be
defined, and this should be easily checked.

strSQL = "SELECT JobNo FROM tblPourItem"
strSQL = strSQL & " WHERE JobNo = '" & strJobNo & "'"

This is a very simple SQL statement that queries the table tblPourItem, and
retrieves the JobNo column, but only for rows where the JobNo equals the
value in the variable strJobNo (again defined somewhere in your code).

What else do you want to know? Don't expect us to be able to teach you SQL
over the NG, just not feasible.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Augustus" wrote in message
...
Thanks, first of all.

I didn't know it is a SQL thing until today (i figure out
lat night that the strSQL is passing a string to
somewhere).

My skill at VBA is very limited. i have done alot of
programming.
Could either of you please tell me how do you know that it
is SQL, and not other form like Jet or ODB?

Please give me a bried explaination on SQL, if you don't
mind?
Thanks
Augustus
-----Original Message-----
I am asked to reprogram a Excel/VBA program, and need
help
regarding the following code which querying an Access
Database on the server:


Set Conn = CreateObject("ADODB.Connection")
Conn.Open strADOName
strSQL = "SELECT JobNo FROM tblPourItem"
strSQL = strSQL & " WHERE JobNo = '" & strJobNo & "'"


Could someone please direct me to a source where I could
get information relating to querying external DataBase,
like in the above example, what's the function of SELECT?

Especially this line:
strSQL = strSQL & " WHERE JobNo = '" & strJobNo & "'"
WHat is the purpose of ' ?

Also, is there a function in ADO that would let me
retrieve the file's path? Cause I need to find out the
date last modified to Conn(in the example, which is
located on the sever).
.

While oConn connection is open:

oConn.Properties("Current Catalog").Value

--

"Bob Phillips" wrote in message ...
Augustus,

There are a number of bits that you did not give us.

Set Conn = CreateObject("ADODB.Connection")

This simply creates an ADO connection object.

Conn.Open strADOName

This is where the connection is opened. As you can see, it uses a variable
strADOName, which is what are called the connection parameters, (server,
data source, id, password, etc). Somewhere in your code this muse be
defined, and this should be easily checked.

strSQL = "SELECT JobNo FROM tblPourItem"
strSQL = strSQL & " WHERE JobNo = '" & strJobNo & "'"

This is a very simple SQL statement that queries the table tblPourItem, and
retrieves the JobNo column, but only for rows where the JobNo equals the
value in the variable strJobNo (again defined somewhere in your code).

What else do you want to know? Don't expect us to be able to teach you SQL
over the NG, just not feasible.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Augustus" wrote in message
...

Also, is there a function in ADO that would let me
retrieve the file's path?