Linked tables-connecting to backend Access 2000 db with Opendatabase

Linked tables-connecting to backend Access 2000 db with Opendatabase

Post by Peter Janse » Wed, 07 Jun 2000 04:00:00



In case anyone is interested the reason my code wouldn't work was that I
didn't declare which type of recordset I was using in my Dim statement, so
Access VBA assumed that it was an ADO recordset, rather than DAO (and I was
using a DAO Database object...).  Maybe this was because the reference to
Microsoft ActiveX data objects was before that to DAO...? or maybe I don't
know what I am talking about.  Anyway changing this:
Quote:>     Dim rsMaxPreviousID As Recordset

to
Dim rsMaxPreviousID As DAO.Recordset


Quote:> Hi,

> I an trying to make a form that connects to data in a replicated database.
> Following what I believe to be good design guidelines for this kind of
thing
> I am trying to split the frontend and backend.  I need to retrieve data
from
> the table my form is connecting to, and I could do this fine by setting a
> database object to Currentdb, when my form was in the same database as the
> table.

> Now that I have exported the form into a database that uses linked tables
I
> am unable to get the *y data from the table.  I thought I would be
able
> to use the OpenDatabase method but I can't get it to work.  I want to
> connect to the db in the simplest way possible and I definitely don't want
> to use a system DSN.

> I am a bit of a newbie to this sort of stuff, so if someone can suggest a
> better way to achieve this I would be greatful.  The error message I am
> getting with my current code is a type mismatch.  The database object
seems
> to be created ok, it's just wont open a recordset...it seems the
> openrecordset method is returning a non recordset type??? I don't know
> what's happened and am at the tearing my hair out stage....

> Can anyone help....please...

> Private Sub NewRecordInitialisation()
>     Dim objdb As Database, objConnection As New ADODB.Connection
>     Dim intIDNum As Long, strSQLForMaxID As String
>     Dim rsMaxPreviousID As Recordset
>     Dim objUsersTable As TableDef

>     'Set objdb = CurrentDb
>     strFullPath = CurrentProject.Path & "\" & "malls2000.mdb"

>     Set objdb = OpenDatabase(strFullPath)

>     strSQLForMaxID = "SELECT MAX(ID) FROM Users WHERE (ID > 70000) AND (ID
<
> 99999)"

>     Set rsMaxPreviousID = objdb.OpenRecordset(strSQLForMaxID)

>     If Not (rsMaxPreviousID.EOF) Then
>         rsMaxPreviousID.MoveFirst
>         If IsNull(rsMaxPreviousID) Then
>             intIDNum = 70001
>         Else
>             intIDNum = rsMaxPreviousID(0) + 1
>         End If

 
 
 

1. Problem with a Link SQL 7.0 Table to an Access 2000 DB

Hello,
We've got an application in VB 6.0 and ADOX that works with an Access 2000
DB or a SQL 7.0 DB. So we need to link the tables to a work DB in Access
2000 before executing it. We don't have any problems with Access 2000 but
when we attach SQL Tables to our work DB we can't add a record, for example.
Actually the linked tables are read-only tables. It seems like a query that
doesn't have the primary key of a table. If we attach the table manually
inside the work DB it works, so we think that there are a hide property to
solve this problem.
Our connection string is:
...
Tbl.Properties("Jet OLEDB:Create Link") = True
Tbl.Properties("Jet OLEDB:Link Provider String") =
"ODBC;DSN=Pubs;UID=SA;PWD=;Server=SRV004;"
Tbl.Properties("Jet OLEDB:Remote Table Name") = "xxxxxx"
...
We've compared the properties of the same table linked manually or by
program and they are exactly the same, so we don't know what we have to do.
Does anybody know an idea?

Thanks in advance.

2. how to use MAPI in vfp

3. Connecting to Linked Access to SQL Server BackEnd database

4. Got error "not enough resource from ASP pages"

5. Problem when linking a table from SQL 2000 into Access 2000

6. NYC - Strong Powerbuilder w/Oracle or Sybase - PB, GUI Design (F/T and Contractors)

7. Linking to an SQL Server 2000 table that uses bigint with Access 2000

8. Need help in filling some openings

9. Write Conflict Error-Access 2000 SR-1 Link Tables to SQL Server 2000

10. Accessing reports in Access 2000 with SQL 2000 as Backend

11. call ACCESS 2000 report from SQL server 2000 without link table

12. Write Conflict Error-Access 2000 SR-1 Link Tables to SQL Server 2000

13. HELP!!..accessing access 2000 with linked SQL Server tables