select * from fails to work

Set db = OpenDatabase(vb6) ;’ This bit I know is wrong and pulls up an odbc menu, I’ll fix that later
maxtables = db.TableDefs.Count
For k =1 To 9 'maxtables
List1.AddItem db.TableDefs(k).Name & Space(99) & db.TableDefs(k).Fields.Count
DoEvents
Next

That worked fine

mytable = “Address Table”

SQL = "SELECT [diary code] FROM " & mytable

I wanted to have sql="select * from " & mytable and thought it would return all the fields in address table.

Set rs = db.OpenRecordset(SQL)

at this point I get a syntax error. Any idea why? I am unable to select anything!

Tom

Tom,

You should state in your post that it is Visual Basic you’re speaking about - I know it, because I answered your previous questions, others don’t [;)]

First, why do you need to populate List with hundreds of Navision tables - but its up to you, of course.

There is no table named “Address Table”, suppose you provided it as sample, but even then it should be “[Address Table]”, as with field name, containing spaces…

PS Question is a little bit offtopic (VB in SQL forum), but I’m glad I can still remember what I did ~15 years ago and help someone out… [:D]

Hi Modris,

I had been making good progress with this until I lost my laptop. I am getting back on track but today I have struggled with this.

I am writing a VB6 Application to read and act on a navision database.

Feel free to move thos post to a more appropriate section if you want to.

I was hoping that the ususal sql commands that I use in vb to talk to MS Aacess would be ok but "select * from [address table] order by [post code] " is failing al the time.

In the last half hour I have gon into the dsn config and selected all filelds and now I can’t get connected at all! I am about to change that back.

My earlier tests were to read ALL of the tables in tha database and peek into some of them to find out what fields are where as I havbe no documentation from the original nmavision develpopers

Thanks for taking the time to read this.

Tom

this is the short version

If I list the tabledefs I get a list of tables in the database but when I try tp "select * from [one of those names] I get tthe error message

3554.modris1.JPG

Here is the code that produced the picture above

Set db = OpenDatabase(vb6)
maxtables = db.TableDefs.Count
For k = 0 To maxtables - 1
List1.AddItem db.TableDefs(k).Name
DoEvents
Next
’ SQL = “SELECT [diary code] FROM [address table]” 'this also failed
SQL = “select * from [address table]”
Set rs = db.OpenRecordset(SQL)

Th error tells me the Jet db engine os trying to find the table…is that part of my problem? I am using a system dsn called vb6dsn and I have to browse to it each time I run the program

Maybe some definitions are out of scope in subroutine where you try to SELECT?
If List fills normally, the other should work, too

Besides, for exploring purposes, you can crate a MS Access database and LINK through ODBC the Navision tables.

Jesus, we are discussing VB in Navision forum [:D] I’ll move the thread from SQL, while Joerg hasn’t thrown us out … [;)]

This is VB problem then, not Navision’s.

You should fully define your ODBC connection in VB code (as this is experiment, you can allow yourself to hard-code the parameters like DSNname etc) and make it GLOBAL, otherwise it will be out of scope in your Button’s OnClick event procedure (or how it was called - 15 years, anyway [*-)] )

Besides, what Jet DB has to do with ODBC - or I have forgot everything completely…

I thought it was a valid sql question…what do I know?

Thanks for keeping me right with this, I’m really floundering at this point.

Ayway, I know it SHOULD work. and I thought that seeing AL of the code involved would let you spot the mistake right away, I guess not. This has me baffeled, I think it should work but not haviong connected to navision before I wondered if there awas something odd to look out for. I have created the system dsn as I can’t work out where to put the company name and server name if i use ADO.

I’ll keep reading old posts to find inspiration.

how can this only party work?

Set db = OpenDatabase(vb6) 'The vb6 name is not relevant, I can type anythoing in there as I get prompted for the system dsn anyway
For k = 0 To maxtables = db.TableDefs.Count -1
List1.AddItem db.TableDefs(k).Name
Next

That produced a list including the table called “address table” in list 1

Set rs = db.OpenRecordset(“select * from [address table]”) At this point it fails!

Tom

The variables are all in the same procedure, I am runing it all from one button so I can’t see how anythong can get out of scope.

I am sure I had this working with the code I lost on my laptop. It was a simple rs.openrecordset(“select * from customers”) and it worked really well.

Hi,

got it sort of fixed. I removed all references to ado from the project and it now works.

Weird!

Thanks, I’ll be back later in the week with more strange requests I am sure.

Tom