CODEWITHSUNDEEP
ms-accessvba
PiggyInTheMirror
PiggyInTheMirror

Reputation: 77

Inserting records in MS Access by means of macros

Good evening!

At this moment I'm learning to work in MS Access for my job purposes. I gained some understanding of the program's basics, such as creating tables or making easy forms (though not yet working ideally), and by now I've got stuck in solving the following task.

I have a database BooksDatabase, which consists of three tables: Books, Authors and AuthorsInfo. First one contains information about books (name, genre, country, release year etc.), third one is about authors (first name, last name etc.) and the second one links ever book with its author(s). The task is to import data from text file to those tables, so that it would be almost automatic. I understand how to import files to MS Access (at least, the ones of *.txt extension) and I do this into the table BooksToImport, but I have some difficulties with inserting imported data. Here is the code of my function ImportBooks(), which I execute from macros of the same name:

' Procedure which imports data about books from the table BooksToImport
Function ImportBooks()
 Dim dbBooks As Database
 Dim rstImBooks, rstBooks, rstAuthors, rstBALink As DAO.Recordset
 Dim codeI, codeB, codeA, codeL As Variant
 'initializing database
 Set dbBooks = CurrentDb
 Set rstImBooks = dbBooks.OpenRecordset("Query_BooksToImport",dbOpenDynaset) 'receiving data from query
 'checking if the query has any records
 If rstImBooks.RecordCount = 0 Then
  MsgBox "There are no records for importing!", vbInformation, "Attention!"
  rstImBooks.Close
  Set dbBooks = Nothing
  Exit Function
 End If
 'if it's OK, we're making a loop on query's records
 rstBooks = dbBooks.OpenRecordset("Books",dbOpenDynaset)
 rstAuthors = dbBooks.OpenRecordset("AuthorsInfo",dbOpenDynaset)
 rstBALink = dbBoks.OpenRecordset("Authors",dbOpenDynaset)
 rstImBooks.MoveLast
 rstImBooks.MoveFirst
 Do While rstImBooks.EOF = False
  'checking if there is a book in out database with the same name as in imported data
  codeB = DLookup("[ID]","[Books]","[BookName] = '" & rstImBooks![BookName] & "'")
  If IsNull(codeB) Then
   'inserting new record
   With rstBooks
    .AddNew
    ![BookName] = rstImBooks![BookName]
    .Update
    .Bookmark = .LastModified
    codeB = ![ID]
   End With
  End If
  'in much the same way we're treating the data about authors and making the links
  rstImBooks.MoveNext
 Loop
 rstImBooks.Close
 rstBooks.Close
 rstAuthors.Close
 rstBALink.Close
 Set dbBooks = Nothing
End Function

I have two problems with this function:

  1. method .AddNew for rstBooks is not working — MS Access shows me a message with error 438 ("Object doesn't support this property or method");
  2. also I cannot assign variable rstBALink to the recordset because compiler says "Invalid use of property".

So my question is this: how should I solve these two problems? What do I do wrong that my function is not working properly?

Upvotes: 0

Views: 106

Answers (1)

MoondogsMaDawg
MoondogsMaDawg

Reputation: 1714

A few issues with your code that I see. These may or may not fix your problem.

Your declarations are implicit, meaning you aren't being specific with your code about what your recordset objects are. Instead of using:

Dim rstImBooks, rstBooks, rstAuthors, rstBALink As DAO.Recordset

Try:

Dim rstImBooks As DAO.Recordset
Dim rstBooks As DAO.Recordset
Dim rstAuthors As DAO.Recordset
Dim rstBALink As DAO.Recordset

You can put them all on one line separated by commas, but you still need to declare the type for each or Access will assume it's a variant.

Secondly, recordset objects need to be created using the Set keyword, not by using an = alone.

This was done correctly in the top portion of your code, but is incorrect here:

rstBooks = dbBooks.OpenRecordset("Books",dbOpenDynaset)
rstAuthors = dbBooks.OpenRecordset("AuthorsInfo",dbOpenDynaset)
rstBALink = dbBoks.OpenRecordset("Authors",dbOpenDynaset)

Should be:

Set rstBooks = dbBooks.OpenRecordset("Books",dbOpenDynaset)
Set rstAuthors = dbBooks.OpenRecordset("AuthorsInfo",dbOpenDynaset)
Set rstBALink = dbBooks.OpenRecordset("Authors",dbOpenDynaset)

I think that will solve your issues, but I didn't review every line of your code admittedly. Let me know if you still have problems.

EDIT: Found a typo:

rstBALink = dbBoks.OpenRecordset("Authors",dbOpenDynaset)

Should be:

Set rstBALink = dbBooks.OpenRecordset("Authors",dbOpenDynaset)

(missed an 'o' in dbBooks)

Upvotes: 1

Related Questions