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!
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]
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
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
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!