sql express and CurrentDB

[phil]

Hope someone can help

I'm just starting to use SQL Express rather than Jet with Access 2000

link up and work ok except when it comes to VBA

If I enter the following VBA code

dim rst as dao.recordset
set rst = currentDB().openRecord("table")

CurrentDB() always returns NULL
Ive included the reference to DAO 3.6

Does anyone know why this is or what I should be doing to open recordsets
with VBA

Any help will be much appreciated

Phil

 

[Albert D. Kallal]

dim rst as dao.recordset

set rst = currentDB().openRecord("table")

CurrentDB() always returns NULL
Ive included the reference to DAO 3.6

I never seen the above syntax ever work.

first, are you talking about a mdb file, or a ADP project?

if you talking about a mdb, then

set rst = currentDB().openRecord("table")

That needs to changed to

set rst = currentdb.OpenRecordSet("table")

 

[phil]

sorry, I mistyped the message (I'll be more careful in future)

the code was
....
set rst = currentdb.OpenRecordSet("table")

but currentDB seems to return NULL for some reason

Thanks for the reply
Phil

 

[Stefan Hoffmann]

hi phil,

I'm just starting to use SQL Express rather than Jet with Access 2000

As Albert already asked: Are you using a MDB or an ADP?

link up and work ok except when it comes to VBA

What do you mean by "link up"?

CurrentDB() always returns NULL

Use

?(CurrentDb is nothing)

in the immediate window. It is always True in ADPs and always False in MDBs.

Ive included the reference to DAO 3.6

That's not necessary. The usage of CurrentDb creates automatically an
hidden reference to it.

In an ADP you should use CurrentProject and CurrentProject.Connection.

But as Microsoft says:

Access 2007 and SQL Server

Access creates front-end applications that leverage SQL Server as a
backend data source. Access forms and reports can be optimized as
efficiently as Visual Basic front-end for SQL Server. Office Access 2007
offers two ways to connect to SQL Server data: linking to SQL Server and
Access Data Projects (ADPs).

The preferred way to connect to SQL Server is MDB file format or ACCDB
file format. This enables you to use the full flexibility of local
tables and local queries, while leveraging the full power of SQL Server.
In addition, MDB and ACCDB files link to multiple SQL Servers and a wide
variety of other data sources. Office Access 2007 contains many new
features available in both MDB and ACCDB file formats, but only a subset
of those features are available in ADPs.

http://technet2.microsoft.com/Offic...ba1c-446a-8ff2-221769a58ba51033.mspx?mfr=true

mfG
--> stefan <--

 

[phil]

OK many thanks for the link, it's always helpful to see the recommended way
of working


I am very happy and experienace using Access (with Jet) but mainly for
internal applications. We are now looking at a relatively simple application
to ship to our customers and would appreciate any tips on which platform to
use.

It seems to me the fastest developement would be Access+SQL EXPRESS and
possibly packaging everything up with the Office Developers Kit.

The other option is to create a Visual Basic system linking to SQL-EXPRESS

is there any real advantage in ditching Access and using Visual Basic - it
looks like much harder work in VB

If Access is better why to people use VB to develop database applications?

Many thanks for all the help - much appreciated

Phil

 

[phil]

Hi Stephan

Many thanks for the link, it's always helpful to see the recommended way
of working


I am very happy and experienced using Access (with Jet) but mainly for
internal applications. We are now looking at a relatively simple application
to ship to our customers and would appreciate any tips on which platform to
use.

It seems to me the fastest development would be Access+SQL EXPRESS and
possibly packaging everything up later on with the Office Developers Kit.

The other option is to create a Visual Basic system linking to SQL-EXPRESS

Is there any real advantage in ditching Access and using Visual Basic - it
looks like much harder work in VB

If Access is better why to people use VB to develop database applications?

Many thanks for all the help - much appreciated

Phil

 

[Stefan Hoffmann]

hi Phil,

If Access is better why to people use VB to develop database applications?

Imho there are two points to consider:

- Distribution of the application
It is harder to get a clean rollout of an Access application in the wild.
An application build as stand alone application with VB or in my case
Delphi is easier to distribute.

- Usage of external components
As far as i know, you can get a lot more external components for VB or
Delphi than for Access.


mfG
--> stefan <--

 

[phil]

thanks again

I think we'll proceed with Access and SQL-EXPRESS

I just find the whole, table, query, report,vba system so easy to use. I've
tried VB on its own and it feels like hard work

Thanks again
Phil

 

[Robert Morley]

- Usage of external components

As far as i know, you can get a lot more external components for VB or
Delphi than for Access.

Virtually any component that integrates into VB will also integrate into
Access, unless it's something geared more towards design-time.



Rob

 

[Stefan Hoffmann]

hi Robert,

Virtually any component that integrates into VB will also integrate into
Access, unless it's something geared more towards design-time.

VB for Applications is not VB. There are differences, which sometimes
won't show and sometimes crash Access/VBA. It depends also on the
techniques behind the components (ActiveX or API).


mfG
--> stefan <--

 

[Robert Morley]

This is true, but I've rarely come across anything that doesn't work in
both. It happens, but not often.



Rob