vba code to create table in ms access 2003 Results

Sponsored Links:

How to trough VBA code to create dynamic table in MS Access 2003? Any sample code is welcome. TQ

Hi All - I had a simple little VBA code written that works for MS Access 2003 but not for 2007. I believe it has to do with the menu names. Basically it was a list box that toggled the menus on and off. Here is the working code that I've tried to tweak by adding 'yes visible' for anything under the sun.

I tried also adding menu names that I thought were obvious ('Create' menu 'External Data' menu and so on) but it bombs out saying it cannot find those menus. So you'll see below I've commented those out. That big list of menus doesn't turn anything up, but we still have the odd 2003 user so I've kept them in.

As it stands now, clicking on the list box hides and unhides the 'Home' menu, but I also want it to show the other standard menus that appear when you open Access normally ('Create' 'External Data' and 'Database Tools' are the ones missing).

So as far as I can tell, I just need those standard menu names but can't find them. Help!

	Private Sub listVisible_AfterUpdate()
Dim intVisible As Single

intVisible = Me.listVisible
If intVisible = 1 Then
DoCmd.ShowToolbar "Menu Bar", acToolbarYes
DoCmd.ShowToolbar "Formatting (Form/Report)", acToolbarYes '----yes to this
DoCmd.ShowToolbar "Form View", acToolbarYes '----yes to this
   DoCmd.ShowToolbar "Toolbox", acToolbarYes
   DoCmd.ShowToolbar "Database", acToolbarYes
   DoCmd.ShowToolbar "Ribbon", acToolbarYes
'   DoCmd.ShowToolbar "Create", acToolbarYes
'   DoCmd.ShowToolbar "External Data", acToolbarYes
'   DoCmd.ShowToolbar "Database Tools", acToolbarYes
       DoCmd.ShowToolbar "Menu Bar", acToolbarYes
    DoCmd.ShowToolbar "Database", acToolbarYes
    DoCmd.ShowToolbar "Relationship", acToolbarYes
    DoCmd.ShowToolbar "Table Design", acToolbarYes
    DoCmd.ShowToolbar "Table Datasheet", acToolbarYes
    DoCmd.ShowToolbar "Query Design", acToolbarYes
    DoCmd.ShowToolbar "Query Datasheet", acToolbarYes
    DoCmd.ShowToolbar "Form Design", acToolbarYes
    DoCmd.ShowToolbar "Form View", acToolbarYes
    DoCmd.ShowToolbar "Filter/Sort", acToolbarYes
    DoCmd.ShowToolbar "Report Design", acToolbarYes
    DoCmd.ShowToolbar "Print Preview", acToolbarYes
    DoCmd.ShowToolbar "Toolbox", acToolbarYes
    DoCmd.ShowToolbar "Formatting (Form/Report)", acToolbarYes
    DoCmd.ShowToolbar "Formatting (Datasheet)", acToolbarYes
    DoCmd.ShowToolbar "Macro Design", acToolbarYes
    DoCmd.ShowToolbar "Utility 1", acToolbarYes
    DoCmd.ShowToolbar "Utility 2", acToolbarYes
    DoCmd.ShowToolbar "Web", acToolbarYes
    DoCmd.ShowToolbar "Source Code Control", acToolbarYes
    DoCmd.ShowToolbar "Ribbon", acToolbarYes

DoCmd.ShowToolbar "Menu Bar", acToolbarNo
DoCmd.ShowToolbar "Formatting (Form/Report)", acToolbarNo '----yes to this
DoCmd.ShowToolbar "Form View", acToolbarNo '----yes to this
   DoCmd.ShowToolbar "Toolbox", acToolbarNo
   DoCmd.ShowToolbar "Database", acToolbarNo
   DoCmd.ShowToolbar "Ribbon", acToolbarNo
End If
End Sub

how does one code vba module in ms access to

* query table for top 10 records in all fields (ideally randomized sample but i know that can get tricky)

* repeat for all tables in local .mdb

* and export output to excel (table name, field name, corresponding records)

basically, i am trying to create a sample file so i can know which tables and fields to pull records for ultimately


I am trying to create a chart in en Excel file from Access VBA and am having great troubles with it. I am using Access 2003, SP3 and have Microsoft Excel 12.0 Object Library reference in VBA that is being used in it. My MS Office version is 2007 however. I am able to create an Excel file and put data into it without a problem, but once i start with chart creation the problems ensue. I have an older version of code that I am simply reusing that is quite complex and creates a chart out of a pivot table data that itself uses an external source pointing to Access tables through ODBC. That code works but on a different machine that uses MS Access 2003 with MS Excel 11.0 Object Library and Excel 2003, not 2007. Here is the code that does not work in a shape of two procedures one of which is just a main program that calls the procedure that does the work. The commented out code are all different approaches that I also tried and that did not work. If anybody can tell me why this is happening I would be quite grateful.

Sub TestUpdateCreateOutputSpreadsheet()

' First argument is path so put the directory path of the Excel file there
' The second argument is the name of the excel file (.xls extension is assumed)
OpenDataFileAndCreateChart "C:Documents and Settingssg0441667My Documents", _
"Output spreadsheet_test"
End Sub

Sub OpenDataFileAndCreateChart(ByVal pathNm As String, ByVal wbNm As String)
'~~~ Required Excel Application object to open new Excel Window ~~~
Dim xl_Output_app As Excel.Application
'~~~ Required Excel Workbook object to open new Excel Workbook with ~~~
'~~~ in the opened Application window ~~~
Dim xl_Output_wb As Excel.Workbook
'~~~ Required Excel Worksheet object to add new Excel Worksheet in ~~~
'~~~ opened Excel Workbook ~~~
Dim xl_Output_ws As Excel.Worksheet
Dim xl_Output_chrt As Excel.Chart

'~~~ Create the application object and assign to the declared object ~~~
Set xl_Output_app = CreateObject("Excel.Application")

'~~~ Application made invisible so that user can't modify the data ~~~
' xl_Output_app.Visible = False

'~~~ Create the Workbook object and add to the declared Workbook object ~~~
Set xl_Output_wb = xl_Output_app.Workbooks.Open(pathNm & "" & wbNm & ".xls")

' Create graph
' Dim xl_output_srs As Excel.Series

'~~~ Change the name of the second existing worksheet object and use it to create the graph ~~~
' Set xl_Output_ws = xl_Output_app.Sheets(2)
' xl_Output_app.Sheets(2).Activate

' xl_Output_ws.Name = "Graphs"
' Set xl_Output_chrt = xl_Output_ws.ChartObjects.Add

' xl_Output_chrt.Location Where:=xlLocationAsObject, Name:=xl_Output_ws.Name
' xl_Output_chrt.SetSourceData Source:=xl_Output_wb.Sheets(1).Range("A1:B59"), PlotBy:=xlColumns
' ActiveChart.SetSourceData Source:=ActiveWorkbook.Sheets(1).Range("A1:B59")

' Set xl_output_srs = xl_Output_chrt.SeriesCollection.NewSeries
' xl_output_srs.Values = xl_Output_wb.Sheets(1).Range("B1:B59")
' xl_output_srs.XValues = xl_Output_wb.Sheets(1).Range("A1:A59")
With ActiveChart.SeriesCollection.NewSeries
.Values = xl_Output_wb.Sheets(1).Range("B1:B59")
.XValues = xl_Output_wb.Sheets(1).Range("A1:A59")
End With

xl_Output_wb.SaveAs pathNm & "" & wbNm & "_1" & ".xls", FileFormat:=Excel.XlFileFormat.xlWorkbookNormal

' Close the Excel workbook and application
Set xl_Output_app = Nothing
End Sub

The code works up to the ActiveChart.Charts.Add statement and starts failing after that .....

Thanks in advance


Hi, I'm new to Ms Access but I can manage to create a database with a littl ebit of queries, tables and reports as weel as forms. Can anyone help me in giving me a link where i can get vba codes which I can copy paste for appropriate function?

Please help

Hi all

I am trying to write the code to delete an existing table, create a new one linked to a SQL Server db using OLE DB Provider connection string and Trused Connection

unfortunately I get an error 'could not find installable ISAM' when I try to append my new table top the tabledefs collectionin my Access DB (MS Access 2003).

Quote: Dim sConnStr As String
Dim oTable As TableDef
Dim sDestinationTable As String
Dim dbs As Database
Dim tbl As DAO.TableDef
Dim tblLinked As DAO.TableDef

sDestinationTable = "SQL Rulesnew"
Set dbs = CurrentDb
CurrentDb.CreateTableDef "SQLRules_" & VBA.Format(Now, "ddmmyyyy")

sConnStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Hermes_Rep;Data Source=fos"

' below command also wouldn't work so commented!
'DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;Driver={SQL Server};Server=Fos;Database=Hermes_Rep;Trusted_Con nection=Yes", acTable, "[Report SQLRulesnew]", "SQLRules" & VBA.Format(Now, "ddmmyyyy")

' if table exists, delete it
If DCount("*", "MSysObjects", "[Name]='[SQL Rulesnew]' AND [Type] In (1, 4, 6)") > 0 Then
DoCmd.DeleteObject acTable, sDestinationTable
End If

Set tblLinked = dbs.CreateTableDef(sDestinationTable)

tblLinked.Connect = sConnStr
tblLinked.SourceTableName = sDestinationTable
dbs.TableDefs.Append tblLinked
tblLinked.RefreshLink notice that the source table name is [Report SQLRulesnew] so I believe I have to enclose it in square brackets is that right?

Also, is it possible to just iomport the data without a link?

I tried using Docmd.TransferTable with acImport but no luck with that either.

thanks for showing me where I am going wrong.

Hi everybody,

First of all, a word of warning, my knowledge of MS Access is severely limited, please bear with me.

I’m working for a company during my university summer break. I have been tasked with creating a database (thank god my boss doesn’t want anything fancy) for storing the minutes of management meetings, which should also include a comfortable search engine.

So far, I have created two tables:

The first is the master table (tblSitzung) which contains the following columns with information regarding the meetings itself (I’ll write down the original German titles of the columns and provide a translation in brackets)

sitzung_id (the meeting-ID, which is the primary key)
sitzung_datum (the date of the meeting)
sitzung_art (management level involved)
sitzung_teilnehmer (participants)

The second table (tblThemen) contains the minutes and has the following columns

themen_id (the ID of the respective item on the agenda, the primary key)
sitzung_id_f (the key with which the table “tblThemen” is linked to the master table)
sitzung_fragestellung (the question raised in this particular item on the agenda)
sitzung_input (well, the input from the participants :-)
sitzung_ergebnis (the course of action that has been agreed upon)

Now, the tricky part is creating a search form. Since I lack any understanding for VBA code or queries, I have spent days looking for a solution in various forums. I do realize that there are plenty of examples of how to create a search form out there, but none of them seems to be suitable for my particular requirements. Well, maybe they are, but since I don’t really understand VBA, I haven’t really been able to figure out how to customize sample codes so that they fit my database.

What I want to do is create a form that basically consists of a simple text field and a “search” button. The database user should be able to enter a word into the text field and then click on the search button. The search should be performed within the columns sitzung_fragestellung, sitzung_input and sitzung_ergebnis in the table “tblThemen”. Then, a new form should pop up which includes the respective item (or items) of the agenda that include the word entered in the search field plus the date, the participants and the kind of the meeting. So, if a keyword is found in the column “themen_fragestellung”, the search result form should also include the respective fields of “themen_input”, “themen_ergebnis” plus the “sitzung_datum”, “sitzung_art” and “sitzung_teilnehmer” that belong to it. It would also be great if the keyword was somehow highlighted in the field it was found in.

I don’t know if what I have envisioned is easy to accomplish or not. I do hope that it is easy, so that anybody of you who is willing to help me out won’t be busy for too long.

I will upload a sample database filled with some junk data, which consists of the tables I have explained above.

I would be greatly indebted to anybody who has a look at it and could perhaps include the search form as outlined above. Just coming up with a VBA code for the search form would be great as well (I guess I could take it from there once I have the code :-)

Any other input, hints or ideas would be greatly appreciated as well!

I apologize in advance if I have misjudged the scope of the problem and asked something of you that would require too much input on your part.

I hope to receive feedback soon! If I need to clarify something, please let me know!

Best regards,


At some point, you may find it necessary to use VBA to get your job within a database done. Also, there are cases where some databases model a complex set of business rules and requirements and you must use VBA generously.

The most important thing to understand is that there are no truly hard and fast rules regarding how to be a good programmer. However, since the time of vacuum tubes, several programmers have observed patterns and thus contributed to a set of guidelines that help other programmers, regardless of whether they are using C, COBOL, Java, Visual Basic, or any one of many programming languages available.

This article deals in generalities and a bit of programming theory. The overall objective is to provide a maturing programmer with a framework to work within when considering how they will implement solutions to the problem at hand. Thus, the article is intended to call your attention to *potential* best practices, but it is still *your* responsibility to decide whether you will apply these recommendations or not.

Have hammer, will see nails

The first thing to consider is a caveat: VBA is truly a wonderful way to extend your application's functionality, and it can be tempting to turn to it for problems for which there exists a better solution without need for VBA. There's a saying- if your only tool is a hammer, everything starts to look like a nail. Thus, just because it's possible in VBA, it doesn't mean you should do it in VBA. SQL has its niches, and so does Access's built-in functionality and bound forms/reports/controls. Therefore, you may want to make a point of asking yourself what is the right tool for the job before swinging away.

Topics CoveredNaming Conventions & Reserved Words Option Explicit and Variable Declaration Data Types do matter Scope of Action Another Meaning of Scope Compile early and compile often and test your code just as often How do I code this? Using Constants, Functions, and Properties Eight atomic procedures are better than one big sub Using class modules to maximize reusability and reduce work
Naming Conventions & Reserved Words

Our first step is to decide on a naming convention. It actually doesn't matter what you ultimately decide on. The real criteria here is that this is a convention you are able to stick with throughout the life of project. It has failed as soon as you stray away from the convention because you found something more convenient.

The most commonly used convention among Access developers is Leszynski-Reddick style. That should give you an idea of what is usually included in such conventions.

Furthermore, you should be aware of 'Reserved Words'; it may seem intuitive to name a field storing dates "Date", but how would Access know if you're talking about a field named "Date" or a function named "Date"? This kind of naming can create confusion and unpredictable behavior. Generally, if there's a function, object, or property that has a name, it's a reserved word and thus should be avoided (or at least prefixed/suffixed).

Here's a list of reserved words that will serve as a good starting point and give you a sense of what constitutes a reserved word.

Option Explicit and Variable Declaration

The single thing that will do the most to help you to code efficiently is to require variable declaration for everything.

This can be set by Tools -> Options. On the dialog, select Editor tab and check "Require Variable Declaration"

This will automatically insert a line at top of every module:

	Option Explicit

This protects you from accidentally creating extra variables that you did not intend. For example,

	Public Sub TryMe()

Dim st As String

sr = "foo"

End Sub

Without the option explicit, we would end up with two variables, st which is never set, while sr now has value of "foo" which should have been set for the variable st. With Option Explicit, this would raise an error and thus call your attention to the troublesome variable which you can then take steps to correct.

Another benefit of using Option Explicit is that it helps to reinforce the good habit of specifying a data type explicitly. The default data type for any variable is Variant when we do not specify a type. While variant has its uses, it is relatively expensive because VBA has to resolve the type every time you use a variant.

	Dim x 'This will be initialized implicitly as a variant. Not good.
Dim y As Integer 'Better now we have an explicit data type

Data Types do matter

As alluded to earlier, it is important to be very precise in what data type you use. Each data type has its costs in terms of memory consumption and time to process that data type.

Generally, it is best to use the smallest data type that fits your needs. For instance, an Integer data type will serve you well if you do not need a number less than -32768 or greater than 32767.


	Dim obj As Object 'May be iffy
Dim wrksht As Excel.Worksheet 'This is more explicit

Declaring a generic Object data type is also expensive because, like the Variant data type, VBA has to resolve the capability of data type Object before it can use it. For programmers who actually need late binding, this is a legitimate use.

The bottom line is that you need to know various data types that are available to you and be explicit when defining variables. This also works for disambiguating different libraries. Suppose we used both ADO and DAO (a problem area for those using Access 2000, 2002, and 2003 with MS flip-flopping between the libraries). There is nothing wrong with using them both as long we disambiguate:

	Dim drst As DAO.Recordset
Dim arst As ADODB.Recordset

There is one more point to remember when working with data types- it's easy to end up with several different types when you think you're using just one. This operation actually involves two data types:

	Dim dtMyDate As Date
dtMyDate = "2008-31-10"

Which has two data types, but VBA will automatically coerce the string to a date for you, but this may not be always what you want. Therefore, there are instances where you will have problem with data types. It will be useful for you to remember that not everything is what it seems to be. This is especially true when you are dealing with objects such as controls or recordsets where you may set various data type to its various properties; being careless can have unintended side effects.

Furthermore, if it happens that you have two different data types but need to perform operations with both, it's usually a good idea to be explicit in casting them.

	MyString = MyInt + MyDbl* 
'How does VBA know if you wanted a Double stored as a String or an Integer stored as a String?
MyString = Cstr(CDbl(MyInt) + MyDbl) 
'This explicitly tells VBA that you want your Integer cast to Double, added to another Double, then cast as a String.

Scope of Action

One simple concept becomes incredibly important all too soon when dealing with VBA. That concept is "scope of action" - which in programming terms, means - how far-reaching is this program's operation? How much do you want it to do? How much data will it have to "touch" to accomplish its goal?

At one end of the spectrum, we can talk about a bit of VBA code that is associated with a single control on a single form, with the intent of making the control's background pale green when a number is positive or pink when the number is negative. This is a very simple code requirement that probably has the smallest possible scope of action. In VBA terms, the hardest part here is knowing where to put the code, which will probably not exceed five or six lines including the IF statement and its two main branches - one for the positive case and one for the negative case.

At the other end of the spectrum, VBA can read a directory, open a file, control a loop that reads records, import data to a table via Recordset operations, and update a progress bar on a display while ALL of the above is going on. Or it can open an Excel Workbook to read various cells. It can open a Word document to perform searches and statistical operations. It can do all of these at once.

With this incredible range of action, VBA can be your friend for all sizes of problems. However, there are times when VBA is more of a hindrance than a help. For beginners, the temptation is prevalent to write some VBA code to convert some complex data from one format to another. However, an update query can often do this without any VBA code at all. OR you can write the VBA code as a function that could then be used by a query. That means that the data conversion would be in VBA code but record navigation would be done via SQL.

Therefore, one useful skill is to know whether some action is or is not within the range of actions available within VBA. It is recommended that any person wanting to solve a problem with VBA should first know other aspects of Access and SQL syntax. VBA can and will help you "re-invent wheels." This sometimes leads to a glut of wheels, which isn't necessarily a good thing.

Another Meaning of Scope

VBA actions can have implied scope. However, the VBA routines themselves also potentially have a specific scope in terms of "visibility." The visibility of a VBA function or subroutine depends on where it is defined and who can call it (activate it). Scope of visibility, in simplest terms, is "who/what can see it?"

Access supports two types of VBA (code) modules. These are the standard modules and the class modules. A standard module is accessible from an object shown in the Module tab of the database window. Code in a general module could be activated from any form or report you have defined.

Forms and reports have class modules. All private procedures in a class module is visible only from the object associated with that module, and public procedure are accessible only when the object is open. A closed form doesn't exist in the list of forms. (It is, however, in the "Documents" or "AllForms" collection. But that's a concept for another lesson.)

A common mistake is to define a function in a class module for form A and expect form B to be able to use that function. If you want to reuse a function in multiple forms, it must be stored in a general module. This, too, is a practical application of the concept of "scope."

One fine point: The scope of action for a piece of VBA code does not have to match the scope of visibility for that code. But some authorities suggest that the two should be related by your choice of module design.

Therefore, an issue to consider when you are thinking about using VBA is to decide HOW you want to use it - from many places or one – and how much you want to do with it - narrow scope of action or wide-open scope of action. "Ambitious" is a word that accurately describes a novice VBA programmer wishing to write his/her first VBA subroutine to have a wide scope of action from a class module.

So when you are contemplating VBA as a solution, ask yourself two questions. (1) Who/what will need to do this? (2) To what must this be done when run from each of the possible answers to #1? THEN and ONLY THEN are you ready to think about the steps required to implement a VBA solution to your problem.

Compile early and compile often and test your code just as often

The biggest favor you can do to yourself is to make a habit of compiling your code frequently. This aids the process of debugging because you confine yourself to only last few lines you added since the last successful compilation.

Likewise, you definitely want to get in habit of testing your code often. By focusing on writing one procedure at a time and testing it, you can make the process simpler. Of course, this is not always possible, so discretion is required in determining what constitutes smallest block of code that can be meaningfully tested.

This is especially true with "Object or With variable not set" or "Too few parameters; expected 1" and similar run time errors. Run time errors are those not caught by the compiler, which only looks at syntax errors. Thus, it may be not always clear what triggered the error in first place. You do not want to see that error after having written a hundred lines of code without having had compiled & tested the code.

How do I code this?

A common situation that crops up even for most experienced programmers is they have a process that they haven't quite coded for and don't know the appropriate methods, properties, or statements to execute the statement. When you consider that Windows APIs collectively can have 60,000 different procedures, it should become apparent that even the C programmer has to know how to get the data he needs to program, just as much as we need to programming in VBA.

Therefore, a good habit is to get quite familiar with help files. I even reference help files for several methods that I've already used just to make sure I am aware of all possible side effects of using those methods. At this point, I should note that for Access 97, help files is quite extensive, but not as in 2000 & 2003. In case of 2003, I actually find that using search from the Visual Basic Editor to get the appropriate information is more effective than using the search in Access UI.

Another useful tool is to use Object Browser (can be called by pressing F2 or Views -> Object Browser), which is very useful for a quick perusal of different libraries, objects contained inside each of libraries and what arguments they take and/or return.

Also, it bears repeating that for any problem you are having right now, someone had it before you and in all probability already solved it, so it is usually a matter of searching effectively on various websites. Sometimes they are contained in whitepapers, technical documentation, and/or kb/msdn articles provided by Microsoft. Sometimes they are just a post on a bulletin board. The greater length you are willing to go through to read the fine print and test it out, the better programmer you will be.

Finally, there are times where it's just beneficial to test for yourself instead of running on a wild goose chase or waiting for an MVP to swoop in and rescue you or even if you just plain don't understand. This also implies that your debugging skills should be honed so you can be fully informed when examining the result of the test case. Tips on debugging is beyond the scope of this paper but there are several resources detailing different tools used in debugging.

Using Constants, Functions, and Properties

A rule of thumb is that whenever you have a magic number, a formula or something that exemplifies your business rules, it belongs as a constant, function, and/or property of a module.

For magic numbers such as Pi of a circle, Euler's constant 'e', or anything that will never change ever, constants makes the most sense. (If you need a magic number to be stored persistently but not immutable, the lookup table may be more appropriate.)

	Const Pi As Double = 3.14159

With numbers that you use as a part of business rules... say, calculating tax rates, functions are more appropriate.

	Public Function TaxRate(SubTotal As Decimal) As Decimal

TaxRate = SubTotal + (SubTotal * CurrentTaxRate)

End Function

Note that the 'CurrentTaxRate' isn't defined in that function- that is because it is up to you to decide the best place to store such information. Perhaps querying a table holding history of all tax rates is what your business requires. Or the business may be content having one tax rate that is updated periodically. Regardless of how you decide to implement this, functions are big time-savers.

A prime example is when you need to use calculated controls. It's easy to say "Why write a function when I can just do it all in a calculated control with the control source set to "=SubTotal + (SubTotal * CurrentTaxRate)"?

Well, that is true. Now say that you have 100 of them. Now you've been told that you must charge a shipping & handling fee after the tax. Now you could go and change every one of 100 calculated controls, or you could just go to the function and change it once and all the 100 controls will work as expected. Your decision.

Ideally, your programs should be a bunch of black boxes that can be strung up with a given string so you do not have to go through the arduous process of figuring out which one line causes an error in a giant main function (and there are plenty of errors that will raise on one line but is actually caused by a bad line that could be several lines earlier).

Furthermore, by far the biggest advantage of using functions (and subroutines as well) in a standard module is that it's incredibly easy to debug. You do not even need to open a form to debug it. You do not need to run the application and test it as if you were an end user to debug it. You only need to run it either by clicking the play button or calling it from immediate window (the small window at the bottom of the Visual Basic Editors):

Result will be printed in the immediate window

Property is like Public variables (aka Globals) but is much more versatile and provides you with more control over how it can be manipulated.

A good example would be when you want to use a listbox and its rowsource needs to be changed for every record. Instead of referring to the querydef and recordset every time you fire the OnCurrent event of the form, you can just make it a property and actually concentrate on modifying the parameters. This is what it would look like in the form's module:

	Private rst As DAO.Recordset

Property Get qdf() As QueryDef

'MyQuery is a Parameter Query with the SQL:
'PARAMETERS lngPrimaryKey
'SELECT a FROM table WHERE b = [lngPrimaryKey];
Set qdf = CurrentDb.QueryDefs("MyQuery")

End Property

Private Sub Form_OnCurrent()

qdf.Parameters = Me.MyPrimaryKey 'Set the parameter
Me.MyListBox.Recordset = qdf.OpenRecordset

End Sub

Note that qdf is a read-only property. You can refer to it from everywhere within the form's module (actually, from anywhere as properties are public by default but can be set private if so desired), and if you need to change the name, there's only one place to do it. Everything else will just work™.

Eight atomic procedures are better than one big sub

A common mistake for beginning programmers is to beam with pride over their one-hundred line subroutine. Rarely, if ever, should any procedure should be that many lines. This usually results because you try to wrap a process into a single procedure, when each procedure should in theory represent single task.

It's much easier to write out a procedure that does only one thing but does it well, and then pipe several simple-minded procedures together to generate a complex output. In fact, that is how programmers have been doing it for decades. Suppose we had a process that required us to extract a set of data, format it into something, then export it out. It would be easier to write one sub:

	Public Sub Foo()

Extract it!

Format It!

Export It!

End Sub

But this is better for a production environment:

	Public Sub foo()




End Sub

Public Function Extract(MyData As Arguments)

Extract It!

End Function

Public Function Format(MyData As Arguments)

Format It!

End Function

Public Function Export(MyData As Arguments)

Export It!

End Function

Now, a case can be made that that the second approach is more work than first one because you now have to write four procedures instead of one. However, by writing four smaller procedures, you significantly simplify your debugging process because you only need to know that your function works as expected then forget it. Furthermore, down the road, you may discover that you needed a similar task for a different process... Well, golly, it's a function so it's ready to go! Increasing reusability of your code will make you more productive.

Also, it's easier to go back five years later and re-read the atomic functions and understand what they do, rather than reading the big sub and wondering what the heck you did there. So in this sense, the second one leads itself to better documentation without requiring you to write comments as would certainly be required in first example.

Using class modules to maximize reusability and reduce work

One common task that crops frequently is automating Excel and manipulating its spreadsheet. Thus, it's not uncommon to see code for initializing such automation such as this:

	Private Sub foo()

Dim objExcel As New Excel.Application
Dim objWorkBook As Excel.WorkBook
Dim objWorkSheet As Excel.WorkSheet

Set objWorkBook = Excel.OpenWorkbook("MyWorkBook")
Set objWorksheet = objWorkBook.Worksheets(0)

'Do something....

Set objWorksheet = Nothing
Set objWorkBook = Nothing
Set objExcel = Nothing

Whew, that was a lot of work managing several objects, especially if all we wanted to do was to export a bunch of data in a specific worksheet for same workbook. And then multiply that by every time we need to create a new procedure to deal with different aspects (e.g. maybe to read off a worksheet for example, or to add new worksheet or whatever).

Some may use public variables to help resolve this issue but that has its own set of problems. One good example is if you set a public variable to this worksheet but later refer to it thinking it was set for that worksheet, you have a problem.

A Class module is a perfect solution in this circumstance. You only need to declare one line:

	Dim MySpreadSheet As New MyExcelClass

And the class module will run through the steps to initialize the application for you, retrieve the workbook and worksheet for you from this single line. Depending on what methods and properties you give to a class module you can even make it as simple as:


In which the method AppendNewData (written by you) already knows the correct position to place the new data, so you only need to write the class module once, then just refer to the methods of that class module. Intellisense (that tiny scrolldown menu that pops up to help you complete code) also will work for your custom class modules.

If you find yourself needing classes; Here's a primer. Generally, your code should aim to be highly cohesive yet loosely coupled.


Whenever you turn to VBA to fill in the niches to meet your business requirements, it is possible for you to start out with a generic procedure that encapsulates the task at hand. When you have a rough draft, it become more apparent how you can refactor your code into black boxes that are 1) easy to use, 2) can be used for several different tasks, 3) are self-documenting or self-evident. Developing those habits will serve you well in long time.

Last thing. Whenever a solution seems out of reach, or the light bulb above your head turns on, no matter how brilliant and crystal clear it is, you want to first ask yourself: Who else has had this problem or task in front of them, and how did they solved it? Their solution is just a search away. By doing this, a solution can probably be found to your insurmountable problem, or if others solved similar tasks differently than you had planned to, then maybe your planned solution is not the best way to approach your task. Paraphrasing AWF's geoergedwilkinson: "People are very clever in their ability to twist VBA or Access to do easy things in the hardest way possible". A critical eye is what saved me from running down a long alley only to hit a wall or re-inventing unnecessarily complicated wheel when there already exist a nice & simple wheel.



New to VBA in msaccess.

Please assist.

I would like to create a stored procedure in msaccess 2003 which has a parameter of a table name in the msaccess database (mdb).

I have 12 tables each for one month of the year and need to run queries on each table.

A stored procedure I believe will assist with saving time, but I am at a loss of where to start.

a sample sql query looks like this:

Sum(Balance_31032005.m_gl_bkbal_base) AS SumOfm_gl_bkbal_base
FROM Balance_31032005
GROUP BY Balance_31032005.c_category, Balance_31032005.c_product
ORDER BY Balance_31032005.c_category, Balance_31032005.c_product;

The table name being Balance_31032005 for March 2005.

Any help would be gratefule.

Kiran Harry.

ps. if there are any development tools for MS Access that can facilitate writing the code your advise would be greatly appreciated.

I'm creating VBA code in a new Access project to automatically produce a test environment. The database I am attempting to create a test environment for is a split database; meaning the GUI and the data are in two seperate .mdb files.

I am using Access 2003. The new Access project is to:

1. Copy the production version the live database into another folder (DONE)
2. Rename the production tables with the prefix "Prod_" (DONE)
3. Make structural copies of all the tables in the newly copied database (QUESTION)

I am using TransferDatabase to make the structural copies. The code below is as such:

	Dim acc As Access.Application
    Dim aobj As AccessObject
    Dim i As Integer
    Dim strName As String
    Set acc = New Access.Application
    acc.OpenCurrentDatabase (testDBLocation)
    For i = acc.CurrentDb.TableDefs.Count - 1 To 8 Step -1
        If Left(acc.CurrentDb.TableDefs(i).Name, 5) = "Prod_" Then
            tableToCopy = acc.CurrentDb.TableDefs(i).Name
            tableToCreate = Replace(acc.CurrentDb.TableDefs(i).Name, "Prod_", "")
             DoCmd.TransferDatabase acExport, "Microsoft Access", testDBLocation, acTable, tableToCopy, tableToCreate, True
        End If
    Next i

I get the following error when I am attempting to copy the tables: Run-Time Error 3011 - The MS JET Engine could not find the object "Prod_zz"....

The problem it seems is that I'm using a mediatary DB (I'll it C) to transfer information from the live DB (label it A) into a copied DB (label it B).

How do I use the VBA function TransferDatabase to copy information from A into B using Project C?

After upsizing access database to sql server I found a few problems.
My configuration is: Win xp, access 2010 (but I still using 2003 format mdb). SQL Server is Express 2008 R2 located on win server 2003 x64.
Connection is established through ODBC.
Upsizing tool didn't report any mistakes.
The first problem I found is with linked tables. In Access (before upsizing to sql) I created relationships between tables and works fine, but, after upsizing when I try to open linked table I received message: "The table or querry name 'dbo.tablenam' you entered in either the property sheet or macro is misspelled or refers to a table or querry that doesn't exist."
Relationships not working with sql server (gray button), and when I try to use Diagram, diagram also inform me: "The diagram cannot be opened because MS Access 2010 does not support database diagrams for the version of SQL Server to which your database is attached."
I started SQL server management studio on server and created diagrams. After that, nothing is changed, I receiving same error on linked tables.
I checked table properties - extended properties - MS_SubdatasheetName and correct table name is writen in Value field.
But, I noticed that the table name in error message is wrong. Table name must be 'dbo.tablename' not 'dbo.tablenam', and same situation is for all linked tables, the last letter is missing.
I have experimented with adding dot (or any other letter) to the end of name of subdatasheet in Value field, and after that, linked table working perfectly.
This solution for me is temporarily satisfactory, but I'm not sure that is correct way for solving this problem.
Any ideas?

Other problems is in vba code.
I'm using form to display record about our computers on lan, and on that form
I put command button which opened datasheet with some related information (local users) for that particular computer.
That code works fine in access (before sql), but now after pressing command button I receiving: " Run-time error '102': Incorrect syntax near ';'. ".
My code is:

Private Sub OpenLocalUsers_Click()
Dim sWHERE As String
sWHERE = "[Computer]= '" & Me.Computer & "'"
DoCmd.OpenForm "FLocalUsers", acFormDS, , sWHERE
End Sub

Pressing Debug button lead me on
DoCmd.OpenForm "FLocalUsers", acFormDS, , sWHERE
row. I remove ", , sWHERE", and after that not receiving any error message, but instead of related records I see whole Localusers table.
I'm not good in vba proggraming, anyone can help?

I haven't used 2007 hardly at all, so my wish list is based on what I see in 2003.

All suggestions so far, with number of people who voted for each one:
-Bound treeview and listview controls, or at least wizards to assist with the loading
-Ability to show different image for each record in a continuous form (1)
-Ability to change color and/or shape of command buttons
-Provide more configuration options to tweak the behavior, even if it has to be done with a CLI, *ESPECIALLY* for ODBC/OLE DB behavior and bound form behavior (e.g. saving early or late, etc) (1)
-Allow for form and control templates
-Better tab control (e.g. transparent ones *and* using Windows Theme for example)
-Corollary: Make sure that Access controls inherits from Windows' controls (I understand that Access controls are just lookalikes... confirmation?)
-Restore the new look and feel in 2007 back to 2003 (1)
-Better integration with BLOB types (sound, picture, and video) with Active X controls
-Provide a means to open a .doc/.xls file directly and define fields for records graphically to simplify the automation and using Word/Excel's native functionalities with convenience of Access's bound controls. (1)
-easy interface to integrate ribbon use instead of the XML fiasco (1)
-Continuous form on a continuous form (1)
-Web integration
-Word properties in Access
-Built in or at least intuitive drag/drop functionality

-Editable crosstabs (1)
-Retain formatting in SQL view of query, allow commenting
-Union queries in the QBE
-Drill down into sub-queries in the QBE (3)
-Provide a mean to use read-only query as a recordsource for a bound form/report, and a separate but updateable recordsource for inserts/updates. (1)
-Give *real* paramaterized queries/prepared statement a la Oracle/MySQL/MS SQL

-Completely eliminate macros as they currently exist. Adopt the Excel/Word macro paradigm (using VBA) and institute a macro recorder. Integrate Application level commands into the "macro" recorder (i.e. split a database, etc.). (2)
-Invoke the QBE within VBA and format SQL from QBE into VB ready string. And/or do a direct dump of SQL from QBE to a VB formatted string.
-Provide Application Events (e.g. OnOpen and OnExit for Access environment) (1)
-VBA commands for *everything* that you can do in the frontend, like splitting database, etc., and/or better documentation of such commands if they already exist
- update the vba code when using the command button wizard

-Better security, restore ULS, provide a hook into logging so we can specify custom logging form or do some extra processing with security (e.g. authenticating for backend at same time) (1)

-Ability to turn mde into exe (2)
-Use different registry keys for different versions of Access so that running multiple versions doesn't cause problems (1)
-Make Access completely separate from the rest of MS Office, instead make it an Add-On to Visual Studio
-Make it fully object-oriented (1)
-Reinforce good programming habits (e.g. have a sane naming convention for starters), controls on form should have a different default name than the field names when created ... maybe using some kind of prefix such as cbo, txt (2)
-Remove the 355 byte limitation (per field) on exporting data to Excel.
-remove/hide certain features which are considered "bad practices" such as lookups at table level
-make Access much more robust with regards to networking and provide error handling for networking issues (5)
-better native database backend

I am not a programmer or IT professional, so please forgive me for not knowing how to frame my question in a professional way. (note: i don't really know VBA, either, but sometimes I borrow bits of code from various cookbooks)

-background: I built an MS Access database (to track patients in a clinic), originally in 2003 with a windows xp svc pack 3 environment. Now I have 2007 on my pc, other users still have 2003 (don't know if this is related to issue)

-I split the database; the backend lives in a folder on a shared network drive (I think its a WLAN but not sure), I gave each user (5 users total) a frontend on their individual network drives, except for one user who doesn't have one so its on the same drive as the backend (as is mine)
The frontend contains:
Switchboard to select other forms
- forms for data entry
- forms for generating frequently used reports
- forms for looking up information

Also contains queries and reports, though I tried to hide them from the other users (not totally successfully)

The backend contains:

- one large flat file with data it is all patient-related and doesn't repeat so I didn't see need to split it into more tables
- smaller look-up tables

If special reports are needed, users ask me and I write the query and create the report

FYI I did not turn the front ends into mdp or adp or whatever - on my list of things to do

did set up basic security with workgroup and passwords, tho its not totally airtight
Here's the problem:

- symptom: users were complaining about entries "dropping" or "disappearing" which I at first attributed to user error (have been too busy with other responsibilities to attend to maintenance properly)
- I finally took a closer look, and discovered that there are now multiple replicas of the backend, both in the folder on the shared drive, and in the folders on the individual drives. each is a different size, has different numbers of records, and different verrsions of records (I complied some of them in Excel to compare)

There is no one in our IT support group who is really skilled in MS Access. Can someone please help?

Thanks very, very much,


I have a little experience in VB and also Office 2003, but I am new to VBA and Macros in Access 2007.

What I am trying to achieve here is to update two hidden controls when user clicks the "Save and New" button on the Inventory Details form.

I am using the MS Access 2007 template available from the MS website called "Inventory". You should be able to see it from your Access 2007's "Getting Started" page and then browse for templates under the "Business" category. It is an online template.

If you are able to refer to the "Inventory Details" form, please do so. If not, I'll try to explain clearly.

Okay, so I've added a few fields to the Inventory table to customise it. I've added two 'check' fields called First Entered By and Last Modified By. There are both Text fields.

These two fields are added to the Inventory Details form, and are set to Visible=No to hide them from user visibility.

Now comes the purpose of these two fields. On this form, when the user has entered data for this inventory item's record and then clicking Save and New, it will check whether the First Entered By entry is empty or not. If it is empty, it gets the currently logged in user's name and then put it there. If it is not empty (means it is an existing record being modified by the current user), then it will put the current username in the Last Modified By field.

This is to put a "check" on the record to see it was first created by who and last modified by who. We have to log in with a username before we can have access to this file as it is stored on a server, thus having this "check" field is sufficient. I am not after fool-proof security. Just want to keep it simple as this application is not for profit.

The basic idea is like this:
When User clicks the Save and New button, the Next Record selector or closes the form --> Check if First Entered By is empty or not
> If empty, get Username and enter into the First Entered By field
> If not empty, get Username and enter into the Last Modified By field

However, if all other fields are not edited at all when User closes the form, this check should not be performed which would cause a false alarm and end up creating a blank entry with the FEB/LMB fields filled in.

What I did is to add on to the existing macro in the On Click event of the Save and New button.

Condition                                     Action       Arguments
[Form].[Dirty]                                RunCommand   SaveRecord
[me].[First Entered By].[Value] Is Null       RunCode      =[me].[First Created By].[Value]=fOSUserName()
[me].[First Entered By].[Value] Is Not Null   RunCode      =[me].[Last Modified By].[Value]=fOSUserName()
[Form].[Dirty]                                RunCommand   SaveRecord

That was part of the macro code for the Save and New button at the On Click event.

I know the logic is not there, but that's what I have so far with my limited knowledge.

The problem I'm having now is that when I click the Save and New button, I get the error "The object doesn't contain the Automation object 'me.'.

I am not familiar with the correct syntax for the macro here. I've tried some other ways but keep bumping into the Automation object error.

Could someone kindly help me out? This is my first time working on Access 2007 and touching on Macro. The last time I did Access was a few years back during my studies, and it was very basic.

Thank you in advance!

I've got a block of code that inconsistently generates the following error when exporting data to MS Excel.

MS Access 2000
Windows XP

(1) VBA module connects directly to an Oracle 11g back end table and creates a recordset.
(2) Record set results are used to populate an array (type variant).
(3) Array results are divided so that each element of the array stores a value intended to be written to individual cells in an MS Excel worksheet
(4) A 'matrix transposition' occurs and array values are written to individual cells in an MS Excel worksheet. This is where the run-time error is generated.

When larger recordsets are defined for export I encounter error "1004: Application-defined or object-defined error". Error should not be an overflow problem as I have code that stops export operations if more than 2000 Oracle records are identified for export to MS Excel.

This method is used to export data because it is fast and should be error independent.

Where are things going wrong? Any tips on resolving the error.

	Public Function fnExportToExcel_ADO(strSQL As String, _
    Optional ByRef lnX As Long = 1, _
    Optional ByRef lnY As Long = 1, _
    Optional ByRef lnN As Long = 1, _
    Optional ByRef lnM As Long = 1, _
    Optional blHeaders As Boolean = True) As Worksheet
'// Function: fnExportToExcel_ADO
'// Author: Tranchemontaigne
'//     Based upon code created by Andrew Semenov and retreived in
'//     2003 from http://www.zmey.1977.ru/Access_To_Excel.htm
'//   Modified:
'//     Date        Editor        Description
'// ===============================================================
'//     XX XXX 2003 Tranchemontaigne  -Created
'//     25 Aug 2009 Tranchemontaigne  -Adopted for Death database
'//     28 Dec 2012 Tranchemontaigne  -Added error handling
'//     26 Feb 2013 Tranchemontaigne  -Modified to use a direct
'//                                connection to Oracle
'//                               -Improved no records found error
'//                                message
'//                               -Set a 'too many records' error
'//                                message.  too many records will
'//                                cause an overflow when exporting
'//                                to MS Excel
'// Description:
'//     Function receives an SQL string and outputs the results of
'//     this query to a MS Excel spreadsheet usi9ng ADO recordset
'//     objects.  Andrew Semenov adds the following comments about
'//     about using the ADO objects to export data to MS Excel:
'//     ADVANTAGE: Very fast, reliable and adjustable
'//     DISADVANTAGE: This method is much slowed by the necessity
'//     to transpose matrix received by getrows.  Unfortunately,
'//     getrows puts values in transposed way.  If it can be
'//     avoided some way, speed will increase much.
'//     FEATURES: you have to specify X and y - top left cell, and
'//     in N and M variables you receive the height and width of
'//     range received.  Set Headers variable to true if you need
'//     column headers.
'//     ERRORS: This method is error independent - error values
'//     just ignored.  The components of this solution are ADODB
'//     recordset - used to retrieve records values from query and
'//     put them into array, and ten array is being transposed and
'//     put into MS Excel Range.
'// Requirements:
'//     Microsoft Visual Basic for Applications
'//     Microsoft Access 9.0 Object Library
'//     Microsoft Excel 9.0 Object Library
'//     Microsoft ActiveX 2.8 Data Objects Library
'// Input:
'//     Variable   Type    Description
'//     ===========================================================
'//     strSQL     string  sql statement describing record source
'//     lnX        long    top left cell X position in Excel file
'//     lnY        long    top left cell Y position in Excel file
'//     lnN        long    height of range received in Excel file
'//     lnM        long    width of range received in Excel file
'//     blHeaders  boolean yes/no setting for column headers
On Error GoTo Err_fnExportToExcel_ADO

'Error tracking variable
Dim strCodeBlock As String
Dim strError As String

'Create Excel spreadsheet variables
Dim XL As Object
Dim WB As Workbook
Dim WS As Worksheet

'Export to Excel variables
Dim rst1 As New ADODB.Recordset
Dim rsCon As New ADODB.Connection
    Dim lngRSLoopCount As Long
Dim varA As Variant
Dim varC() As Variant
Dim intJ As Integer
Dim intK As Integer
Dim strConnection As String
Dim varEDW_Credentials As Variant

strCodeBlock = "define recordset and check for results"
Debug.Print Chr(10) & Chr(13) & "strSQL: " & strSQL
    'review EDW login credentials
    varEDW_Credentials = fnReturn_EDW_Credentials()
    'if credentials have not been set, then prompt user to set credentials
    If varEDW_Credentials(2) = False Then
        Call fnOpen_Oracle_Credentials_Prompt
        'determine whether user elected not to provide EDW login credentials
        varEDW_Credentials = fnReturn_EDW_Credentials
        If varEDW_Credentials(2) = False Then Exit Function
    End If
    Set rst1 = New ADODB.Recordset

    'open with a pass through query to the EDW
    strConnection = "Driver={Oracle in OraClient11g_home1};"
    strConnection = strConnection & "Dbq=NHEDWP;"
    strConnection = strConnection & "Uid=" & varEDW_Credentials(0) & ";"
    strConnection = strConnection & "Pwd=" & varEDW_Credentials(1) & ";"
    rst1.Open strSQL, strConnection, adOpenForwardOnly, adLockOptimistic
    lngRSLoopCount = 0
    While rst1.EOF = False
        lngRSLoopCount = lngRSLoopCount + 1
    If lngRSLoopCount = 0 Then
        MsgBox "No records detected for export", vbInformation, "Export to MS Excel Failed"
        Exit Function
    ElseIf lngRSLoopCount > 2000 Then
        MsgBox lngRSLoopCount & " matching records detected were detected for export.  " & _
            "Please refine your criteria to identify less than 2,000 matching records", _
            vbInformation, _
            "Export to MS Excel Failed"
        Exit Function
    End If
strCodeBlock = "Create MS Excel document only if matching records are found"
    'create MS Excel file
        Set XL = CreateObject("excel.application")
        'set number of worksheets in workbook
        XL.SheetsInNewWorkbook = 1
        'make MS Excel file visible
        XL.Visible = True
        'add defined number of worksheet pages to workbook
        Set WB = XL.Workbooks.Add
        Set WS = WB.Worksheets(1)
    'End If

strCodeBlock = "determine result set size"
    varA = rst1.GetRows()
    ReDim varC(UBound(varA, 2), UBound(varA, 1))
strCodeBlock = "populate an array with cell values"
    For intK = 0 To UBound(varA, 1)
        For intJ = 0 To UBound(varA, 2)
            varC(intJ, intK) = varA(intK, intJ)
        Next intJ
    Next intK
strCodeBlock = "matrix transposition"
    lnN = UBound(varA, 2) + 1
    lnM = UBound(varA, 1) + 1
    WS.Range(WS.Cells(lnY, lnX), WS.Cells(lnN + lnY - 1, lnM + lnX - 1)) = varC
strCodeBlock = "column headers inserted if necessary"
    If blHeaders = True Then
        WS.Range(WS.Cells(lnY, lnX), WS.Cells(lnN + lnY - 1, lnM + lnX - 1)).Rows(1).Insert
        For intJ = 0 To lnM - 1
            WS.Cells(lnY, intJ + lnX).Value = rst1.Fields(intJ).Name
        Next intJ
    End If

    Set rst1 = Nothing
    Exit Function
    strError = "Error experienced within " & gstrObject & ": fnExportToExcel_ADO" & Chr(10) & _
        "CodeBlock: " & strCodeBlock & Chr(10) & _
        Err.Number & ": " & Err.Description
    Debug.Print strError
    MsgBox strError, vbCritical, gstrObject & " " & "Error"
    Call fnLogError(gstrObject, "fnExportToExcel_ADO Function", strError)
    Resume Next
End Function