Access Tools

[is98]

Hey all,

I'm doing support with my organization and some of the tasks have been into the world of VBA and Access DB. Now I'm not sure how much you all like it but I'm finding some oddities that I'm trying to work with.
These include, are there any tools for SQL queries? First thing I noticed was, 'where's my good ol' SQL Server Management type of equivalent tool? Like, just pop open a window and run a query?
Are there any handy tools that anyone uses to manage an access db or interface with it?
I'm a competent programmer but I want to make things easier.

Look forward to communicating with y'all

 

[dhookom]

For programming, start with MZ Tools.
You can "pop open" the sql view of a query and enter any valid SQL statement. The query designer also has the design view for drag and drop. There aren't all the views at once like SS Management Studio.

Duane
Hook'D on Access
MS Access MVP

 

[is98]

Are there stored procedures that I can place common code?

 

[lameid]

Are there stored procedures that I can place common code?

Access/JET SQL like SQL Server will run ANSI SQL statemtnets for the most part and supports a lot of additional functions. However there is not conditional or branching in Jet SQL like T-SQL. Instead Access has VBA or 'Modules' for this type of process. In access you can use user defined functions to handle return values in a query (these tend not to convert over to SQL Server well later).

Beyond that some statements and the Access equivalent...

Isnull, NZ
Case When Else End..., IIF (easier to go to CASE WHEN than vice versa)
Field Is Null, IsNull(Field) <-- when is null is used as an operataor not in the where clause

 

[lameid]

I accidently hit submit prematurely....

I want to add is yes you do have code reuse in Access but it has VBA where T-SQL is more everything in one place.

Also an important thing to note about Access is that Queries by default behave like views if Access can figure out how to update data, you can edit it directly via the datasheet.

So in Access all Selects, Insert Intos, Updates, Delete and Make Table statements are SQL / Queries and everything else is in the VBA world. Caveat: you can use UDF's in queries.

Really the differnces come from the fact that different programming objects manage different aspects of the programming.

Until Access 2010 there is nothing like a trigger in Access (I'm still using Access 2003 but if you read the what's new in Access 2010 document, you'll see it).

One last thought... Access also has macros <technically 'triggers' are a type of macro if mememory serves>. They do have some conditional processing abilities but everything exposed by the Macro interface can be done in VBA so serious developers historically have stayed away form them as VBA has more functionality available.