DAO table to access table

[nemi1983]

Dear all,
This is the first time I am doing this, so please excuse me if it is a stupid question or a wrong data import approach.

The problem in my code is to populate an Access table from a DAO table that gets data from an external .csv file (importing trough an Access GUI is impossible).
The idea is as follows: import the csv file as a DAO excel table, manipulate the fields and place them into an access table. The compiler complains about a MIR_tb.MoveNext (table is defined as a dao table)
Code is attached below and any help would be appreciated.


Exists = IsObject(CurrentDb.TableDefs(test))
If Exists = True Then
Do While Not EOF(Check_name)
test.RowSource = "SELECT * FROM MIR_tb"
MIR_tb.MoveNext
Else
MIR_sql_tb = "CREATE TABLE test ([Name] text(50) WITH Compression, " & _
"[Check_name] text(150) WITH Compression, " & _
"[Series_name] text(50) WITH Compression, " & _
"[Reference_date] text(5) WITH Compression, " & _
"[Comments] mediumtext WITH Compression, " & _
"[Check Source] mediumtext WITH Compression)"
Next MIR_w 'Refers to a condition from a previous loop

 

[kjv1611]

Where is this code located, and where's the rest of the code?

Did you declare your variables earlier in the code?

If Exists is meant to be a boolean value/variable, then you could also cut down on a little bit of your code..

If Exists = True Then

Could instead just be this:

If Exists Then

However, I'd caution against using "Exists", b/c it's already used by Access, and could therefore cause issues. See this help bit from Access VBA help (Access 2007):

Exists Method
Description

Returns True if a specified key exists in the Dictionary object; False if it does not.

Syntax

object.Exists(key)

The Exists method syntax has these parts:

Part Description
object Required. Always the name of a Dictionary object.
key Required. Key value being searched for in the Dictionary object.

 

[nemi1983]

Thank you for the hint on code reduction (but I still get the same error) Method of file member not found.
Variable definition:

Dim MIR_tb As TableDef

To answer your questions:

1. All provided variables are defined
MIR_sql_tb as string where I create a local table

So, within this task are several loops. One loops within excel files in a folder on a file system, and other two across rows and across columns of the file. This procedure need to be repeated multiple times on different tables that are produced every month. The idea of this piece of code is to create a table within Access if it does not exist and then populate it with information from files being produced every month.

It might be because I am trying to move fields from a DAO table into an SQL table. If I defined MIR_tb as a recordset, compiler gives an error as well.

 

[kjv1611]

Did you try a DAO.Recordset as apposed to just recordset? I wouldn't think it would make a difference, but it's worth a shot.

 

[kjv1611]

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = ("SELECT * FROM MIR_tb")

'Do your stuff
Do While Not rs.EOF 'specifying the recordset your using..


rs.MoveNext - go to the next one
Loop

'other stuff

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing[/CODE]