VB to Move Named Ranges to Access Database

[hackley01]

I'm writing my first "advanced" VBA code to move Named Ranges from Excel to Access. The Named Ranges are named the same as the Access Database field names.

When I run my code, I receive the following error: "Object required". When I click "Debug", the debugger highlights DoCmd.RunSQL strSql.

The VALUES are the names of the Named Ranges. I have a feeling that's the problem, but I can't find the proper syntax to reference the Named Ranges in VBA. I really appreciate any help you can provide.

Sub toSpreadsDB()

Set adoCn = New ADODB.Connection

adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=I:\REI\Loan Origination\_Loan Origination Tools\Calculators\Spread Database.accdb"

Dim strSql As String
strSql = "INSERT INTO SpreadData([CurrentDate], [DealName], [PropertyType], [MSA], [LoanAmount], [LoanTerm], [Amortization], [IOPeriod], [MarketLTV], [AverageLife], [MosForward], [GrossSpread], [ThreeYrTsy], [FiveYrTsy], [SevenYrTsy], [TenYrTsy], [ThirtyYrTsy], [InterpTsy], [USTRiskAdjSpread], [BPIRiskAdjSpread], [VarBPISprUSTSpr], [WgtAvgBenchSpread], [NetSpreadOverBmark]) " _
& " VALUES('" & CurrentDate & " ', '" & DealName & "', '" & PropertyType & "', '" & MSA & "', '" & LoanAmount & "', '" & LoanTerm & "', '" & Amortization & "', '" & IOPeriod & "', '" & MarketLTV & "', '" & AverageLife & "', '" & MosForward & "', '" & GrossSpread & "', '" & ThreeYrTsy & "', '" & FiveYrTsy & "', '" & SevenYrTsy & "', '" & TenYrTsy & "', '" & ThirtyYrTsy & "', '" & InterpTsy & "', '" & USTRiskAdjSpread & "', '" & BPIRiskAdjSpread & "', '" & VarBPISprUSTSpr & "', '" & WgtAvgBenchSpread & "', '" & NetSpreadOverBmark & "');"
DoCmd.RunSQL strSql

adoCn.Close
Set adoCn = Nothing

MsgBox ("New spread information added.")

End Sub

 

[hackley01]

I managed to get everything working. I defined a variable for each Named Range and was able to get data moving into the Access database. Unfortunately, only the last field moved. Everything else came in as 0 or NULL.

Here is the INSERT INTO statement:

sSQL = "INSERT INTO SpreadData([CurrentDate], [DealName], [PropertyType], [MSA], [LoanAmount], [LoanTerm], [Amortization], [IOPeriod], [MarketLTV], [AverageLife], [MosForward], [GrossSpread], [ThreeYrTsy], [FiveYrTsy], [SevenYrTsy], [TenYrTsy], [ThirtyYrTsy], [InterpTsy], [USTRiskAdjSpread], [BPIRiskAdjSpread], [VarBPISprUSTSpr], [WgtAvgBenchSpread], [NetSpreadOverBmark]) VALUES('" & currentdate & " ', '" & dealname & "', '" & propertytype & "', '" & msa & "', '" & loanamount & "', '" & loanterm & "', '" & amortization & "', '" & ioperiod & "', '" & marketltv & "', '" & averagelife & "', '" & mosforward & "', '" & grossspread & "', '" & threeyrtsy & "', '" & fiveyrtsy & "', '" & sevenyrtsy & "', '" & tenyrtsy & "', '" & thirtyyrtsy & "', '" & interptsy & "', '" & ustriskadjspread & "', '" & bpiriskadjspread & "', '" & varbpisprustspr & "', '" & wgtavgbenchspread & "', '" & netspreadoverbmark & "')"


Set qdf = db.CreateQueryDef("", sSQL)


qdf.Execute dbFailOnError