need sql statement to return table/field names

[MikeT]

I write web pages that access SQL Server and Access databases. Now I need to get into a dBase database, and to top it all off, I don't know any of the table/field names.
I have set up an ODBC connection to the database. All I need now is an SQL statement to return table and/or field names.

Can this be done?

 

[MiggyD]

I've been able to access some of my old dbase 3+ files using VB. So I would presume that it is possible to write SQL statements for record retrieval.

Actually, if you have access to FoxPro, it should be able to read the db. The structure for both db and fp are similar.
--MiggyD

Never be afraid to try something new. Remember that amateurs built the Ark. Professionals built the Titanic.

 

[erkanka]

copy this string as ur connection string

"Provider=MSDASQL.1;Persist Security Info=False;Extended
Properties=CollatingSequence=ASCII;DefaultDir=F:\MUH22;Deleted=1;Driver=
{Microsoft dBase Driver (*.dbf)};DriverId=533;FIL=dBase
5.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=600;SafeTransactions=0;Sta
tistics=0;Threads=3;UID=admin;UserCommitSync=Yes;"

instead of (F:\MUH22) u just have to write down the path where the database folder is

 

[pengprog]

Hi MikeT

I am having the same kind of problems like u. I am trying to use SQL to query DBase tables. Any luck. Can you help me out if you have figured somehting out?

 

[MikeT]

I was able to finally get in by using the actual filename as the table name; if the database were called movies.dbf, I could successfully execute a statement like this:

SELECT * FROM movies.dbf

I don't know if this would apply in your situation, though.

 

[pengprog]

I do know the table/Field names. I get retrieve all the records from my DBase tables but not specific one when i use a where clause. Does anyone know how to fix this.

Here is my code!

Set objRS = Server.CreateObject("ADODB.Recordset&quot
sql = "SELECT * FROM EMPLOYEE.dbf WHERE EMPLOYEE.Id_no = #" & id_num

objRS.CursorLocation = adUseClient
objRS.Open sql, objConn,,,adCmdTable

Error Message:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC dBase Driver] Syntax error in FROM clause.
/intranet/bupp/find_pr_car_records.asp, line 36

Line 36 is the line witht eh swl statement. Is there something wrong with this statement. Does DBase require different SQL statments???

Thanks.

 

[MikeT]

You are using a # symbol in your employee id. That would make it a string versus a number, right? Try this:

sql = "SELECT * FROM EMPLOYEE.dbf WHERE EMPLOYEE.Id_no = '#" & id_num & "'"

You have to enclose the id_num in single quotes if its a string.

 

[pengprog]

Thanks MikeT, however....

Doesn't seem to work either,

I even just made everything really simple with a

sql = "SELECT * FROM EMPLOYEE"

But maybe the problem is not in the SQL statment but the manner in which I open the recordset.

How did you open yours MikeT?

Mine is in the above message.

 

[MikeT]

set oConn = server.CreateObject("adodb.connection")
oConn.Open "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq="&folder&";"
sql="SELECT Name FROM mydb.dbf"
set rs = server.createobject("adodb.recordset")
rs.Open sql, oConn

I'm using ASP to access the database over our intranet site.
These are actually ACT databases. How are you getting access to the db?

 

[pengprog]

Just FYI for people,


I was doing some more testing and my SQL statement:

sql = "SELECT * FROM EMPLOYEE" does work
and the
sql = "SELECT * FROM EMPLOYEE.dbf"
works as well

 

[MikeT]

I also had to rename my databases to 8 characters or less. Anyone else experience this?

 

[pengprog]

Here is my full code:
Error message below that.

'OPEN Connection----------------------------------
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection&quot
objConn.ConnectionString="Driver={Microsoft dBASE Driver (*.dbf)};" & "Dbq=E:\Internet\Intranet\bupp\Tables"

objConn.Open

'OPENED--------------------------------------------

Dim objRS
Dim i, Count
Dim sql

Set objRS = Server.CreateObject("ADODB.Recordset&quot
sql = "SELECT * FROM EMPLOYEE.dbf WHERE EMPLOYEE.Id_no = '" & id_no & "'"

objRS.Open sql, objConn

Error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC dBase Driver] Too few parameters. Expected 1.
/intranet/bupp/find_pr_car_records.asp, line 34

Line 34 is the line objRS.Open sql, objConn

Thanks for all your Help MikeT

 

[MikeT]

Are you sure id_no is being passed ok?
If not, write the sql string out to the screen so you can actually see the statement being executed. i.e.:

sql="..."
response.write sql
response.end

 

[Guest_imported]

Hi Pengprog.

I noticed in your July 23 post that you were using adCmdTable with an SQL string. I think it should have been adCmdText, or left out entirely.