Excel data appended to Access Table via Macro

[kathleen]

I have a large Access database that houses BOM Service Parts data.

1. BUSINESS PROCESS:
- North American Headquarters is notified that a part is no longer an active
production part. Part now becomes a service part
- Upon notification Service Part Specialist goes into Production BOM System
and pulls out the information with regards to this part
- System automatically saves the information to an excel spreadsheet in a
predefined directory


2. I have built an excel application that goes out and reads all files in that
predefined directory

- Macros read the directory
- Opens the files
- Applies business logic
- Formats the data
- Merges all data into one import file

Once all of the files have been incorporated into a single spreadsheet that sheet needs to be appended to the applicable database tables.

How would you recommend I do this such that the macro

- Opens the Access database
- Appends the records to the appropriate table
- Kicks off the queries within access
[/list]

 

[kathleen]

OK so I found this code in the Microsoft Knowledge Base. What do I need to do because my spreadsheet is open and the sheet name is UpLoadFile. In addition I am using xl2k. Really would appreciate any guidance on this.


Sub AppendTable()

Dim db As database
Dim rs As recordset
Dim XLTable As TableDef
Dim strSQL As String

'Open the Microsoft Access database.
Set db = OpenDatabase("C:\MSOffice\Access\Samples\Northwind.mdb")

'Attach the Microsoft Excel 5.0 table "MyTable" from the file
'Book1.xls to the Microsoft Access database.
Set XLTable = db.CreateTableDef("Temp")

'In Microsoft Excel 97, use
'
' XLTable.Connect = "Excel 8.0;DATABASE=...
'
'The rest of the line is the same.
'
XLTable.Connect = "Excel 5.0;DATABASE=C:\My Documents\Book1.xls"
XLTable.SourceTableName = "MyTable"
db.TableDefs.Append XLTable

'Run the append query that adds all of the records from MyTable
'to the Shippers table.
strSQL = "Insert into Shippers Select * from Temp"

'Execute the SQL statement.
db.Execute strSQL

'Remove the attached table because it's no longer needed.
db.TableDefs.Delete "Temp"

db.Close

End Sub

 

[kkknie]

Your example looks to be using DAO (which is older and unfamilliar to me). My example
uses the newer ADO and will require you to add a reference to your excel sheet by going
to Tools | References and selecting Microsoft Active-X Data Objects (any version).

My example also assumes the following:
- You will be appending to all fields in your table.
- Your data is only in rows 1 through 10 (iFirstRow and iLastRow)
- Your data is only in columns 1 through 10 (note the Cells(i,1) through (i,10)
- Your data is only numeric.

Sub InsertIntoDBase()

Dim strSQL As String
Dim strConnect As String
Dim objComm As ADODB.Command
Dim iRow as Long
Dim iFirstRow as Long
Dim iLastRow as Long

iFirstRow = 1
iLastRow = 10

'-Set start and end times and update status bar
Sheets("UpLoadFile").Select

'-Set connection string and create adodb objects
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=\\ServerName\ShareName\DBName.mdb;"

Set objComm = CreateObject("ADODB.Command")
objComm.ActiveConnection = strConnect
objComm.CommandType = adCmdText

For i = iFirstRow to iLastRow

'-Set SQL string to get only M and P row for the time period
strSQL = "Insert Into tblName " & _
         "Values (" & Cells(i,1).Value & "," & _
	              Cells(i,2).Value & "," & _
	              Cells(i,3).Value & "," & _
	              Cells(i,4).Value & "," & _
	              Cells(i,5).Value & "," & _
	              Cells(i,6).Value & "," & _
	              Cells(i,7).Value & "," & _
	              Cells(i,8).Value & "," & _
	              Cells(i,9).Value & "," & _;"
	              Cells(i,10).Value & ");"

'-Execute the SQL 
objComm.CommandText = strSQL
objComm.Execute

Next

Set objComm = Nothing   'Clean up memory

End Sub

Possible Modifications:
-----------------------
-You can change iFirstRow and iLastRow to whatever you want or even get them dynamically from the sheet.
-You can add more columns by just copy/pasting from the sql string.
-You can make a text entry by putting single quotes around the entry
+For example: Cells(i,3).Value & ",'" & _
Cells(i,4).Value & "'," & _ would make the value in column 4 a text value.
-If you aren't setting everything in the table, you will have to specify the fields in the sql statement
+(i.e. Insert Into tblName(field1, field2, field3, field6) Values (etc...
-If you need to insert dates I've found that it is a good idea to put a date conversion in the sql
+(i.e. "CDate('" & cells(i,1).value & "'),"

This doesn't really answer your specific question, but I know that the technique is sound.

K

P.S. The code is not tested, so may have a small bug or two. Test it on a temporary database (please).

 

[kathleen]

Thank you! Am going in now to try. Will advise.

 

[kathleen]

I keep getting an eroor 80040e14

There are 53 columns so I commented out all lines containing the values and tried with just entering the date

strSQL = "Insert Into Parts List " & _
"Values (" & Now() & ");"

still get the infamous error, any clues. Macro steps through just fine to the point of objComm.Execute

Thanks in advance

 

[kkknie]

If you are trying to enter anything other than all fields into a database you must quantify them.

strSQL = "Insert Into Parts List (FieldName1) " & _
"Values (" & Now() & ");"

Next, the date needs to have single quotes around it

strSQL = "Insert Into Parts List " & _
"Values ('" & Now() & "');"


Finally, I would suggest converting the date

strSQL = "Insert Into Parts List " & _
"Values (CDate('" & Now() & "');"

In the future, do a debug.print strSQL to get the string into the immediate window. Then open your database, create a new query with no tables and under View, select SQL and paste the SQL string. Then run your query. You can get the query to work in access, then fit it back into VB.

K

 

[kathleen]

Thanks the debug statement has been of great assistance. I am up to the point now where I have 36 of the 53 columns.

For some reason, I am struggling with an input mask that is defined in the database (#####-#####). Seems to work in one column but not this one. The SQL breaks at that point. The excel sheet contains the - .


Thanks again, will advise of progress.

 

[kkknie]

If the field in access is defined as a number you would first need to strip out the -.

In your sql string substitute Replace(Cells(10,10).Value,"-","") for Cells(10,10).Value

Where 10,10 is the cell you are looking at. Also note that the last parameter is two double quotes sandwiched together.

K

 

[kathleen]

I now have all 53 columns and am being prompted for the append. No problems beautiful! Now I have other issues being:

Key Violations
Lock Violations
Validation Rule Violations


Thanks so much for all your help! I think I can work my way through now.