DAO error when talking to Access 2000 db

[Iblys]

Hi all, this is my first post. I am very frustrated with Microsoft Visual Studio 6 - Visual Basic, I am trying to use DAO to talk to a database that was created with Access 2000 and am having no luck. I have the latest service packs for Office and for Visual Basic and Jet and everything like that, but the code still isn't working.

The database file has a system DSN of "lsdata" and I have tried referring to it using both the DSN and the full path.

Basically what I'm trying to do is to open the database using ODBC so that I can change the structure of it using DAO. I was able to get it to work using SQL but I need more control (you can't add autonumber fields using SQL).

Here is the code:
'First I do this
Dim TheDatabaseThingy as Database
'And I have tried both of these
Set TheDataBaseThingy = OpenDatabase("lsdata", dbDriverCompleteRequired, False, "ODBC;UID=;PWD=;DSN=lsdata&quot
'or
Set TheDataBaseThingy = OpenDatabase("C:\Program Files\Microsoft Visual Studio\VB98\Connexions Project\Connexions Databases\50.mdb", False, False, "ODBC;DSN=lsdata&quot

Both "Set" statements return run time error 3423 which has me greatly puzzled. We have tried exporting it as an earlier version of access, and then we got error 3170.

I am tearing my hair out, any help would be appreciated.

 

[CCLINT]

Why use ODBC for JET?
This is not as effective (limitations) as using JET directly

Check out the JETSQL40.Chm (or the DAO351.Chm) help file and do a search on OpenDatabase
Dim wrkJet As Workspace
Dim dbsPubs As Database

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbsPubs = wrkJet.OpenDatabase("Publishers", _
dbDriverNoPrompt, True, _
"ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers&quot


>(you can't add autonumber fields using SQL).
Sure you can:


And here is how to do it using the DBEngine:

dbsPubs.Execute "ALTER TABLE SomeTable ADD COLUMN MyIDField AUTOINCREMENT"

 

[TheMadJammer]

Iblys,

I had the same problem as you are describing. Eventually I declared a connection variable and used that to open the ODBC database here's some code that worked for me using DAO 3.6.
Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim cn As DAO.Connection

Set ws = CreateWorkspace("", "admin", "", dbUseODBC)
Set cn = ws.OpenConnection _("", , , "ODBC;DATABASE=MyDB;DSN=MYDSN&quot
Set db = cn.Database
Set rs = db.OpenRecordset("Table1", dbOpenDynamic)

This piece of code worked without difficulty and I saved much of the hair on head. I tried CCLINT's solution to no avail. You may have some difficulty with how you open the recordset, (i.e. dbOpenDynamic), because DAO only recognizes certain options depending on the version and whether it Jet Direct or Jet ODBC. But you can figure that out easily enough.

This example is ODBC only. You should not use the ODBC option in the OpenDataBase command if you are specifying the database path.

The Mad Jammer
And then...there was nothing

 

[diamondsc]

MadJammer, your post was helpful with a problem I was having making a connection to a SQL Server back end database from a MS Access 2000 front end app. Now the problem I'm having is selecting an index on a table in the back end. Here is the code:


Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim bill As DAO.Recordset
Dim cn As DAO.Connection

Set ws = CreateWorkspace("", "admin", "", dbUseODBC)
Set cn = ws.OpenConnection("", , , "ODBC;DSN=ESource&quot
Set db = cn.Database
Set bill = db.OpenRecordset("Billing", dbOpenDynamic)

bill.Index = "PK_Billing"
bill.MoveFirst
MsgBox "record " & bill![Record Number]

db.Close

I get runtime error 3251 "Operation is not supported for this type of object".

Can I not select an index on a SQL Server table? If not, how is indexing handled in this case?

Thanks.