Solved: Import Excel with own code.

[Frenchy646]

Hello guys, newbie to VBA and the forum here.
So, here's some background.

Running Access 2003, Excel 2003, and Windows XP sp2 (not possible to upgrade to sp3 or later )
I am used to programming in Java, have dabbled in C, C++ and Assembly Code, so you can assume a basic understanding of programming.

Right, so, here's want I want to do, and what I have (filewise).
I currently have an Excel file, with 14 fields, which are used to define the order and the client.
There is no unique identifier unfortunately, so I have used 12 of the fields, appending and/or modifying them together to make a unique string (pain in the arse, but considering it is possible that any of these fields can be blank,and none of them are unique, I didn't see any other way)
Next, the spreadsheet also has one field for every month, starting from December 2007. Theses fields contain the cost incurred by the specific "client" (as some costs are recurring, and some are installation charges).

Now, I know what you're thinking. Why doesn't he just have a date field or something of the sort ? Well, I don't make the Excel file, I recieve it from the Finance department. And they're not going to change.

I periodically recieve an Excel file ( every quarter ), containing the same fields, except some are added (new ones for new months). Sometimes they go all the way back to 2007, some times not. Theses files do not contain all the info stored in the original Excel file (around 400 row in the file I receive per quarter, a few thousand in the big original file).

What I want to do is create a database so that the user can click on the "IMPORT" button, which prompts them to find the path for the file. (easy part).
Then, here comes the manual import code, which I'm having difficulty with, not really knowing how to do it. I can ask the user to manually delete all the blank rows and columns in the Excel file, but the normal import method still doesn't work.
The way I'm thinking it should be done :
Find file
Look at first row, make unique identifier using my "createPrimary" method, check to see if it exists in the original database.
If it does, just add the new months.
If it doesn't, add a new row (what happens if this new row has more columns than the database, will an append query still work ?! ).

I'm not in the office at the mo, so don't have my hands on the files or code.
Any help/advice appreciated, but what I would like to know is :

Is there any good library, like Java's Sun Library ?(MSDN one is **** and incomplete, dead links, children pointing to parents but not vice-versa ).

What is the best way to operate on excel rows/columns from Access ?( seen multiple ways on the net )

Big post, open to lots of comments ( hopefully )

------------------------------
Light travels faster than sound. That's why some people seem smart until you hear them speak.

[Frenchy646]

Seems my question has baffled the experts then ? :p

[Imdabaum]

Well you have a very complicated issue. There isn't an exact pattern as there is no standardized format you are expecting. So naturally it's going to take a little more time. The greater flexibility you want in an application the greater complexity you will deal with.

[Frenchy646]

True, and I totally understand that Imdabaum.
But I was hoping to get maybe, hopefully, some information on my MSDN library question.
As a graduated computer scientist, I don't like to let people do the programming (although in this case, some code would probably be helpful, as I am useless at VBA ), and would like to fool around with the code until someone can try and give me an answer. Or ask for more details from myself.
As a fellow programmer, I'm sure you can understand my need to play around with the code. I just don't find MSDN at all useful to tell me about the properties and methods of mthe objects :(

[Frenchy646]

To help with providing help here is some visual help (Had to be said)
I'm uploading an example excel file, which is in the quartely format I receive from finance.
The empty rows and columns are used as seperators, and I would delete them before attempting to import.
Also, I do not use the "useless field" in the first row, as I'm not interested in that part of the data. (As you can see, there are less fields than I originally said...I may have forgot that I filled some of the fields in myself, for personal reminders, which I will not do for the database )
Just thought a visual example would be helpful.

For privacy reasons, I am not able to upload an example of the actual file. Nor the names of the actual fields as they provide some form of detail.
The prices are not the real prices either.

[Frenchy646]

Just to update you, I've been very very nice to finance, and they've accepted to only send the months I want.
Hence I can have a pre-determined number of columns (always only 4 months for example ), or a prompted one (I ask them for 6, and enter a value of 6 on my form so that the number of date columns will already be known in a variable in the program).

Although I doubt anyone can help in total with the answer right away, could anyone provide me with a code snippet to cycle through rows/columns of an Excel file to get the values in the cells, from a Access VBA program ? Seen a lot of different methods on the net, and not sure which one is correct/the best

[hansup]

Importing/Exporting Excel is a fairly common operation in Access applications, so not surprised you found so many (too many?) examples.

Your spreadsheet can be imported manually from Access' UI with the Import Spreadsheet Wizard. But sounds like you want something automated.

Since it can be imported cleanly with the wizard, you could do it from VBA code using DoCmd.TransferSpreadsheet method. See Importing Excel spreadsheets from code (http://www.mvps.org/access/general/gen0008.htm) for more information.

Another approach would be to automate Excel from Access. Your VBA would start up Excel and open the workbook file. Then you could create an object reference to your target worksheet, and read the data cell by cell, row by row. See Sample Excel Automation (http://www.mvps.org/access/modules/mdl0006.htm) to get started.

You may prefer that approach if you're "a spreadsheet guy". I'm a database guy, so I would prefer to treat the spreadsheet as a virtual table and open a recordset on it to access the data. I tested the following procedure with my Access 2003 system, using your sample workbook.

Public Sub InspectXcelFile()
Const cstrFolder As String = "C:\Access\webforums"
Const cstrFile As String = "ExampleFinance.xls"
Dim i As Integer
Dim strConnect As String
Dim strSql As String
Dim intFieldCount As Integer

'early binding requires reference, Microsoft ActiveX Data Object Library
' Dim cn As ADODB.Connection
' Dim rs As ADODB.Recordset
' Set cn = New ADODB.Connection
' Set rs = New ADODB.Recordset

'late binding; no reference needed
Dim cn As Object
Dim rs As Object
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
cstrFolder & Chr(92) & cstrFile & _
";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
'Debug.Print strConnect
cn.Open strConnect
strSql = "SELECT * FROM [Billing Summary$] WHERE [Useless Field] Is Not Null;"
rs.Open strSql, cn
intFieldCount = rs.Fields.Count
Debug.Print "Field Count: " & intFieldCount

'list field names
For i = 0 To rs.Fields.Count - 1
Debug.Print i + 1, rs.Fields(i).Name
Next i

'print data from selected fields
Debug.Print
Debug.Print "Record #", "Useless Field", rs.Fields(intFieldCount - 4).Name, _
rs.Fields(intFieldCount - 3).Name, rs.Fields(intFieldCount - 2).Name, _
rs.Fields(intFieldCount - 1).Name
Debug.Print String(8, "-"), String(12, "-"), String(6, "-"), _
String(6, "-"), String(6, "-"), String(6, "-")
i = 0
Do While Not rs.EOF
i = i + 1
Debug.Print i, rs![Useless Field], rs.Fields(intFieldCount - 4), _
rs.Fields(intFieldCount - 3), rs.Fields(intFieldCount - 2), _
rs.Fields(intFieldCount - 1)
rs.MoveNext
Loop

'cleanup
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

This is the data section of the output, which I copied from the Immediate Window:
Record # Useless Field 201006 201007 201008 201009
-------- ------------ ------ ------ ------ ------
1 Data 100 100 100 100
2 Data Null Null Null Null
3 Data Null Null Null Null
4 Data 20 20 20 20
5 Data 20 20 20 20
6 Data 20 20 20 20
7 Data 20 20 20 20
8 Data Null Null Null Null
9 Data 20 20 20 20

However, once you've accessed the spreadsheet data, you need to do something with it ... and I'm stuck at that point.

[Norie]

You say there is no unique identifier?

Why not add one?

[Frenchy646]

Hansup, thank you very much for your example code.
I am in the process of using it to try and do the automation.
You are right, I know about the manual import method, but yes, I do want to do something automated.
As for the DoCmd.TransferSpreadsheet, I know about it, tried it, but didn't seem to work for me, which is why I'm going through the excel file myself with the VBA code (something which I'm sure your code will be very helpful for ! )

Norie
You are right, there isn't a unique identifier in my spreadsheet, however, as quoted below, I have got a vba function to create a UID. (wasn't a fan of the automated one access provides, autonumber, as it was my understanding it would create a new row with it's own UID for every row in a spreadhseet.

so I have used 12 of the fields, appending and/or modifying them together to make a unique string (pain in the arse, but considering it is possible that any of these fields can be blank,and none of them are unique, I didn't see any other way)


As from quarter to quarter I am getting rows regarding the same "client", I would not wish these to be represented as a different row in the table.

Regarding that, what is the maximum string length for a UID in an access table ? Mine is going to be quite long...

[Frenchy646]

However, once you've accessed the spreadsheet data, you need to do something with it ... and I'm stuck at that point.

Thank you again hansup for your code, I have used it to start doing my automation.
Here is what I have so far, I am unsure whether to add a row to a database, I have to use an sql query, or whether I can do it programmatically. Also, I am unsure how to add new rows to the table (if I were to do it programmatically, and not through an APPEND query, as am unsure how to word the query if I were to use it).

Here is what I have so far. It's working on the same example spreadsheet I gave you, minus the empty rows and columns, which I am going to ask the user to manually delete.

Option Compare Database
Option Explicit
Sub openExcelFile()
Dim theFolder As String
Const theFileName As String = "ExampleFinance.xls"
Dim connectString As String
Dim sqlQuery As String
Dim fieldCount As Integer
Dim theUID As String

Dim myConnection As Object
Dim myRecordset As Object
Set myConnection = CreateObject("ADODB.Connection")
Set myRecordset = CreateObject("ADODB.RecordSet")


connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & theFolder & _
Chr(92) & theFileName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

myConnection.Open connectString
'Select all values from the spreadsheet'
sqlQuery = "SELECT * FROM [Billing Summary$] WHERE [Useless Field] Is Not Null;"
'Connects and gets the number of fields present in the sheet'
myRecordset.Open sqlQuery, myConnection
fieldCount = myRecordset.Fields.Count

Do While Not myRecordset.EOF
theUID = createPrimary(myRecordset.Fields(0), myRecordset.Fields(1), myRecordset.Fields(2), _
myRecordset.Fields(3), myRecordset.Fields(4), myRecordset.Fields(5), myRecordset.Fields(6), _
myRecordset.Fields(7), myRecordset.Fields(8), myRecordset.Fields(9))

'If the UID is already present in database, add the new financial data to the relevant row'
'If it is not (i.e Else) then add a new row'

'Note : for a new row, will have to make sure all previous date fields in database are null (i.e all dates'
'till first date row in spreadsheet)'

Loop


End Sub


If you are wondering what my create primary function is, here it is :

Public Function createPrimary(WUselessField As String, WField1 As String, WField2 As String, WDescription As String, _
WField4 As String, WField5 As String, WField6 As String, WField7 As String, WField8 As String, WField9 As String)
Dim f0 As String
Dim f1 As String
Dim f2 As String
Dim description As String
Dim f4 As String
Dim f5 As String
Dim f6 As String
Dim f7 As String
Dim f8 As String
Dim f9 As String
f0 = UselessField
f1 = WField1
f2 = WField2
description = funsies(WDescription)
If WField4 = Null Or WField4 = "" Or Len(WField4) < 10 Then
f4 = "BN99999999"
Else
f4 = WField4
End If
f5 = WField5
f6 = funsies(WField6)
f7 = funsies(WField7)
f8 = WField8
f9 = funsies(WField9)
createPrimary = f0 & f1 & f2 & description & f4 & f5 & f6 & f7 & f8 & f9
End Function
Using the function funsies which is

'A function that takes as input a String, and returns another String consisting'
'of the first letter of every word, seperated by either a space or a hyphen,'
'from the original input String'
Public Function funsies(TheString As String) As String
Dim StrLen As Integer
Dim StringToReturn As String
StrLen = Len(TheString)
For i = 1 To StrLen
If Mid(TheString, i, 1) = " " Or Mid(TheString, i, 1) = "-" Then
StringToReturn = StringToReturn + Mid(TheString, i + 1, 1)
End If
Next i
funsies = StringToReturn
End Function

Note the comments at the end of the first vba code. If, while I browse the net looking for answers, someone could help by giving me an example of how to
- having taken the data from the spreadhseet, compare it with data in the database
-depending on the answer, add new fields to a certain row, (checking if these fields already exist ), or just add a new row to the database. (i.e, do I HAVE to use an SQL query ? ).

And finally, hansup, could you clarify what, in this line,

connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & theFolder & _
Chr(92) & theFileName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

The first and last part of the string are used for. Guessing the final part is something to do with making sure it is an Excel file ?
And I'm guessing Chr(92) is the "/" character.

Cheers, and sorry for the information overload guys, I like to be thorough.

[hansup]

Do While Not myRecordset.EOF
theUID = createPrimary(myRecordset.Fields(0), myRecordset.Fields(1), myRecordset.Fields(2), _
myRecordset.Fields(3), myRecordset.Fields(4), myRecordset.Fields(5), myRecordset.Fields(6), _
myRecordset.Fields(7), myRecordset.Fields(8), myRecordset.Fields(9))


Consider a different approach for createPrimary(). Create a lookup table which has a row for each combination of those fields you feed to createPrimary, and an autonumber field for client_id. Then you can look up those values from the current spreadsheet row to determine the client_id. Store that client_id, instead of all those other fields, in the table where you store the client charges.



'If the UID is already present in database, add the new financial data to the relevant row'
'If it is not (i.e Else) then add a new row'

'Note : for a new row, will have to make sure all previous date fields in database are null (i.e all dates'
'till first date row in spreadsheet)'

Loop

End Sub


Note the comments at the end of the first vba code. If, while I browse the net looking for answers, someone could help by giving me an example of how to
- having taken the data from the spreadhseet, compare it with data in the database

Need more details about the comparisons you wish to perform. Think explicit detail.



-depending on the answer, add new fields to a certain row, (checking if these fields already exist ), or just add a new row to the database. (i.e, do I HAVE to use an SQL query ? ).

So, when your spreadsheet includes a column for 201011 results, you will add a new column to your Access table? And again for 201012 results? And 201101? And so forth?

If you're designing the table structure to match the spreadsheet layout, that is almost certainly a design error. The rule of thumb is "columns are expensive; rows are cheap".

client_id billing_period charge
1 201009 150
1 201010 75
1 201011 200




And finally, hansup, could you clarify what, in this line,

connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & theFolder & _
Chr(92) & theFileName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

The first and last part of the string are used for. Guessing the final part is something to do with making sure it is an Excel file ?

See Connection strings for Excel (http://www.connectionstrings.com/excel)



And I'm guessing Chr(92) is the "/" character.

No. Backslash.

Open the Immediate Window (CTRL+g), type this in, and hit Enter:
? Chr(92)

Then position the cursor on Chr and press F1. That will bring up the Access help topic for the Chr() function.

[Frenchy646]

Consider a different approach for createPrimary(). Create a lookup table which has a row for each combination of those fields you feed to createPrimary, and an autonumber field for client_id. Then you can look up those values from the current spreadsheet row to determine the client_id. Store that client_id, instead of all those other fields, in the table where you store the client charges.

...

So, when your spreadsheet includes a column for 201011 results, you will add a new column to your Access table? And again for 201012 results? And 201101? And so forth?

If you're designing the table structure to match the spreadsheet layout, that is almost certainly a design error. The rule of thumb is "columns are expensive; rows are cheap".





VBA:



client_id billing_period charge 1 201009 150 1 201010 75 1 201011 200



VBA tags courtesy of www.thecodenet.com (http://www.thecodenet.com/)







I know where you are coming from hansup on that subject, but remember, you're talking to a vba newbie. Was kinda scared of using two tables, and autonumber with VBA.

As for the actual format of the two tables...I really should have thought of that.

So, as I see it, rows being cheaper, my new pseudo-code would be like this (I will get to work on it asap)
----------------------------------------------------------------
i=1
Find number of rows with data in the speadsheet, value amount.
For i<amount
1-Get row i of spreadsheet.
---Check to see if lookup table and billing data table exist, if not, create them
---If they do exist, compare the data in the lookup table to the data in the spreadsheet row (string comparison, equal not case sensitive, hyphens, brackets, parentheses and spaces should be taken into account ).
-------There is an exact match in the table
----------Get relative autonumber ID
-------Otherwise, there is not match.
----------Create new row in lookup table, with relevant fields and generate new autonumber
----------Get the generated autonumber
-------While there are financial columns in the spreadsheet.
----------Create new row of financial data in the billing table for each new financial column in the spreadsheet.

Increment i by 1, repeat 1 (for loop )

-----------------------------------------------------------
I am unsure however how to generate autonumbers in vba, but I am sure I will be able to find it somewhere on the net.

[hansup]

I know where you are coming from hansup on that subject, but remember, you're talking to a vba newbie.
Yes, I do remember you told us you are new to VBA. Please understand my concern was focused on your table design, which really has nothing to do with VBA. It's basic relational database issues, which are are common to any database system whether or not the rdbms is packaged together, like Access, with a VBA rapid development environment.


Was kinda scared of using two tables, and autonumber with VBA.
I don't see why you need VBA to manage an autonumber. It's simpler to create an autonumber client_id field in your Clients table, and let the database engine assign the client_id value when you add a client record.


So, as I see it, rows being cheaper, my new pseudo-code would be like this (I will get to work on it asap)
Sorry, but IMO you're jumping ahead. First concentrate on the database schema because it will be the foundation of your application. Flaws in the schema will cause you needless extra work in your application code ... like using VBA to manage an "autonumber" instead of letting the database engine do the work for you.

You want to get data from Excel into Access. Why? How will you use it? Make sure your schema supports those intended uses sanely and in a manner consistent with time-honored relational database principles.

[Frenchy646]

Yes, I do remember you told us you are new to VBA. Please understand my concern was focused on your table design, which really has nothing to do with VBA. It's basic relational database issues, which are are common to any database system whether or not the rdbms is packaged together, like Access, with a VBA rapid development environment.

I know that, but please understand that, finding VBA documentation increadibly lacking compared to the languages that I am used to, I am rather tempted to have a shoddy and expensive database layout that requires less coding, than a proper database layout that requires more coding !


I don't see why you need VBA to manage an autonumber. It's simpler to create an autonumber client_id field in your Clients table, and let the database engine assign the client_id value when you add a client record.

You see, that I didn't know. Of course, if Microsoft Access is smart enough to create a new autonumber automatically without needing prompting when adding a new row to a table programatically, then that makes life easier.



Sorry, but IMO you're jumping ahead. First concentrate on the database schema because it will be the foundation of your application. Flaws in the schema will cause you needless extra work in your application code ... like using VBA to manage an "autonumber" instead of letting the database engine do the work for you.

You want to get data from Excel into Access. Why? How will you use it? Make sure your schema supports those intended uses sanely and in a manner consistent with time-honored relational database principles.

Specifically regarding the autonumber thing, that would not have been a database schema flaw, that was just me not realising that Microsoft Access 2003 could actually manage that (see above).
And as for concentrating on the schema first... A correct schema, that would suit my needs ( which is displaying the total cost per financial year) is very easy to make. It takes all of a half-hour, whereas the automation in VBA, takes a lot longer.
I also ocncentrate on the code first because I do not know the capabilities of VBA, and very much doubt that, given a database, it is possible to do anything you want to programmatically. Which is why I prefer to check that the actual coding is possible first.
However, that may just be because of the lack of documentation for VBA, giving me a prejudicial view.

[hansup]

I am rather tempted to have a shoddy and expensive database layout that requires less coding, than a proper database layout that requires more coding !Interesting. In my experience a proper database design requires less coding; a shoddy design requires more. That applies not just to Access, but to any database platform I've encountered, starting with dBase in 1987.

[Frenchy646]

Interesting. In my experience a proper database design requires less coding; a shoddy design requires more. That applies not just to Access, but to any database platform I've encountered, starting with dBase in 1987.

Let us consider one database, with all the information required in it, with no autonumbers.
All that is necessary is the use of an append query to add new columns (i.e I am referring to my original idea), and a simple function to create the primary key. I say simple, as it uses ideas common to all languages, not anything microsoft related.

Now, let us consider the option I am going for. It requires two tables, and the use of an autonumber.
I think we can agree that the time it takes to program something includes the time needed to research the required method for doing it.
I would have to find out about Microsoft Access autonumbers, and if they require programming, or whether they are done automatically.
I would also have to find out what, if anything, is necessary for coding between two tables in Microsoft Access. If I went for some methods on the net, I would have to open a page, do what I wanted to do, close it, open the other one, do what I wanted to do, and close it, then repeat if I wanted to revert back to the other table.
I would also have to find out if anything needs to be done due to the relationships between the tables.
This ends up taking a lot more time.

You may agree or disagree with me, but I am not the only one who has this view, most of my office agreeing with, including some people who have been using computers since they first came out.
When dealing with an unknown language, with little to no good library, having to deal with less objects in that language ends up in less research, and faster programming.
The outcome is the same, although maybe less efficient, but in some cases, efficiency of memory is not an issue.

[hansup]

You persuaded me that you're firmly convinced you've found the best possible approach for you. :)

[Frenchy646]

Well, I'll have to admit, although I was going on with my first idea, when you mentioned that thing about the second table, a light turned on in my over-caffeinated brain.
It's probably why I was a bit overly aggressive in my responses to you about getting the database format right. I was entirely sure I've got the right one.
I do apologise, stubborness and caffeine don't make for the most polite people.

On another note, two quick questions, either answerable by text or by a link.
What is the best method for running an SQL query on a table, and is it different for a spreadsheet ?
I'm currently doing DoCmd.RunSQl for a query (INSERT) on a table, and myRecordset.Open for a query on a...well, a Recordset.
Also, what object type can I store the result of a SELECT query into ? (on the table, in order to find out if the result is null, or if not, get information on a specific field in the row returned by the query)

And finally, when using two tables that have a one-to-many relationship, with referential accuracy set-up in access, do I need to do anything specific in VBA ?

[hansup]

Also, what object type can I store the result of a SELECT query into ? (on the table, in order to find out if the result is null, or if not, get information on a specific field in the row returned by the query)Look at Access' help topic for the DLookup() function. My WAG is you may find it useful for this purpose. Perhaps the DCount() function, too.

I have no idea how to respond to your other questions.

[Frenchy646]

Hello,
I've now finished my basic programming here, but, surprise surprise, I'm getting an error. In fact, every time I use the 'myDataBase' field, trying to store in it the result of the query using OpenRecordset method, I get an error "Invalid use of property". Obviously because I am using it in a stupid way.
Would appreciate if you could not only tell me how to fix it, or what I'm doing wrong, but provide a link to explain why, or do it yourself if you have the patience and the time.


Option Compare Database
Option Explicit
Sub openExcelFile()
Dim theFolder As String
Const theFileName As String = "Billing_Summary.xls"
Dim connectString As String
Dim sqlQuery As String
Dim fieldCount As Integer
Dim theUID As String
Dim sqlQuery2 As String
Dim myDataBase As Recordset
Dim columnCount As Integer
Dim sqlQuery3 As String
Dim theANumber As Integer
Dim i As Integer
Dim columnName As String
Dim theDate As Date
Dim theValue As Integer

theFolder = "somerandomstuff"
Dim myConnection As Object
Dim myRecordset As Object
Set myConnection = CreateObject("ADODB.Connection")
Set myRecordset = CreateObject("ADODB.RecordSet")

connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & theFolder & _
Chr(92) & theFileName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"



myConnection.Open connectString
'Select all values from the spreadsheet'
sqlQuery = "SELECT * FROM [Billing Summary$] WHERE [Useless Field] Is Not Null;"
'Connects and gets the number of row present in the sheet'
myRecordset.Open sqlQuery, myConnection
fieldCount = myRecordset.Fields.Count

Do While Not myRecordset.EOF
sqlQuery2 = "SELECT * FROM [Customer_Identifiers] WHERE [Useless Field] = " & myRecordset.Fields(0) & _
",[Field1Used]=" & myRecordset.Fields(1) & _
",[Field2Used]=" & myRecordset.Fields(2) & _
",[DescriptionField]=" & myRecordset.Fields(3) & _
",[Field4Used]=" & myRecordset.Fields(4) & _
",[Field5Used]=" & myRecordset.Fields(5) & _
",[Field6Used]=" & myRecordset.Fields(6) & _
",[Field7Used]=" & myRecordset.Fields(7) & _
",[Field8Used]=" & myRecordset.Fields(8) & _
",[Field9Used]=" & myRecordset.Fields(9)
'Do query'
myDataBase = CurrentDb.OpenRecordset(sqlQuery2)


'If sqlQuery2 is not Null'
If (myDataBase.RecordCount > 0) Then
'get autonumber'
theANumber = myDataBase.Fields(0)
'get number of date columns in spreadsheet'
columnCount = myRecordset.UsedRange.Column.Count - 10
'Otherwise'
Else
'add data in lookup table (all fields above)'
sqlQuery3 = "INSERT INTO [Customer_Identifiers] (UselessField, Field1Used, Field2Used, Field3Used, DescriptionField, Field5Used," & _
"Field6Used, Field7Used, Field8Used, Field9Used ) VALUES (myRecordset.Fields(0),myRecordset.Fields(1)," & _
"myRecordset.Fields(2), myRecordset.Fields(3), myRecordset.Fields(4), myRecordset.Fields(5)" & _
"myRecordset.Fields(6),myRecordset.Fields(7), myRecordset.Fields(8), myRecordset.Fields(9) ) "
DoCmd.RunSQL sqlQuery3
'get number of date columns in spreadsheet'
columnCount = myRecordset.UsedRange.Column.Count - 10
'Get the autonumber'
myDataBase = CurrentDb.OpenRecordset(sqlQuery2)
theANumber = myDataBase.Fields(0)
End If
'For loop ==> from 1 to number of date columns'
For i = 0 To columnCount
'Take date string in column, and get value in row'
'Gets the date in form of a String at the top of the column, and converts it into a date format'
columnName = myRecordset.UsedRange.Column.Name
theDate = CDate("01/" & Right(columnName, 2) & "/" & Left(columnName, 4))
'Gets the value in the current column of the current row'
theValue = myRecordset.Fields(10 + i)
'Add new row in billing table : autonumber, date string, row value'
sqlQuery3 = "INSERT INTO [Customer_Cost] (UID, Date, Amount) VALUES (" & theANumber & _
", " & theDate & ", " & theValue & " ) "
DoCmd.RunSQL sqlQuery3
'Need to make an SQL query to add the new "theValue" to the current total in the Customer_Identifiers'
'table with the same autonumber'
Next



Loop


End Sub


If you notice any errors that would only appear during runtime, please do tell, as I haven't tried yet, having spent a lot of time trying to figure out why my current error is occuring.
Also, regarding the last comment in the code, is there a VBA way of doing it easily ? Or do I have to get the value in the field, do the calculation, then do a query to update the value ?

Finally, Hansup, turns out the database format is working well for me. Some contractors messed up, meaning I have to add more functionality to the future forms for the database. Something this format is well suited for :)
Although...it does mean extra work on VBA...

[hansup]

In VBA we need only the = sign to assign a value to a simple variable.
strFoo = "bar" But assigning to an object variable also requires the Set keyword:
Set myDataBase = CurrentDb.OpenRecordset(sqlQuery2)

[Frenchy646]

But assigning to an object variable also requires the Set keyword:



VBA:
Set myDataBase = CurrentDb.OpenRecordset(sqlQuery2)

VBA tags courtesy of www.thecodenet.com (http://www.thecodenet.com/)



Ah, right, that's fair enough then.
Does VBA also have object variables for the simple variables, for complex operations ?
(for example, like the java Integer object which you have to use for methods like ParseInt() )

[hansup]

Does VBA also have object variables for the simple variables, for complex operations ?
(for example, like the java Integer object which you have to use for methods like ParseInt() )
I will guess the answer is no. But I should defer to some who knows Java.