Excel data to Access?

[Norm]

I have a macro to validate monthly data. After the data
is verified, it needs to go to an Access table. I'd like
to do that from Excel and then the user will go to Access
to do the rest of the processing.

Access has a 'TransferSpreadsheet' command, but what would
you suggest to use from an Excel macro to go to Access?
Surely there's something better to use than opening an ADO
recordset and then writing each record.

Thank You!

 

[Guest]

I do the same thing but I start in Access, use a button to open the file in
Excel, make changes and save it, then back in Access import into the Access
database.

For another transfer, I have this process in an excel spreadsheet attached
to a button

I have a cell (2,3) to put the access filename

Sub cmdImport() ' attached to a button on an excel spreadsheet

Dim db As DAO.Database
Dim objRS As DAO.Recordset
Dim qryDef As DAO.QueryDef

If IsEmpty(Cells(2, 3)) Then
MsgBox "You must first select the location of the database.",
vbCritical
Cells(2, 3).Select
Exit Sub
End If

Set db = OpenDatabase(Cells(2, 3))

'put something here .....
' maybe db.excecute

' or use the db.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel4,
"tblImport", txtImportFile, True

End sub

Does that give you an idea to start with?

Lynn

 

[Norm]

Thanks, Lynn! That helps a lot. I didn't think of using
the DoCmd to get the TransferSpreadsheet. Brain is
thinking too much of the upcoming weekend!!

Norm

 

[Jamie Collins]

...

I didn't think of using
the DoCmd to get the TransferSpreadsheet.

I think your instinct was correct. IMO, automating the MS Access app
merely to access data is lousy advice. Using ADO is much more
appropriate.

Surely there's something better to use than opening an
ADO recordset and then writing each record.

Indeed. Open an ADO Connection and execute either an INSERT
INTO..SELECT or a SELECT..INTO command. For example:

For an existing table:

INSERT INTO
[Database=C:\MyDB.mdb;].MyExistingTable
(MyCol1, MyCol2, MyCol3)
SELECT
F1 AS MyCol1,
F3 AS MyCol2,
F5 AS MyCol3
FROM
[Sheet1$];

To import into a new table:

SELECT
F1 AS MyCol1,
F3 AS MyCol2,
F5 AS MyCol3
INTO
[Database=C:\MyDB.mdb;].MyExistingTable
FROM
[Sheet1$];
;

Jamie.

--

 

[Guest]

Jamie,

I am trying to do something similar, I have an access DB that contains
employee information. I need to make a user form in Excel that manipulates
the data in the Access DB. I can handle making the userform and the Access
DB, just wondering if you knew how I would go about connecting and executing
some DML.

thanks,
--
Ben Sullins
(e-mail address removed)

...

I didn't think of using
the DoCmd to get the TransferSpreadsheet.

I think your instinct was correct. IMO, automating the MS Access app
merely to access data is lousy advice. Using ADO is much more
appropriate.

Surely there's something better to use than opening an
ADO recordset and then writing each record.

Indeed. Open an ADO Connection and execute either an INSERT
INTO..SELECT or a SELECT..INTO command. For example:

For an existing table:

INSERT INTO
[Database=C:\MyDB.mdb;].MyExistingTable
(MyCol1, MyCol2, MyCol3)
SELECT
F1 AS MyCol1,
F3 AS MyCol2,
F5 AS MyCol3
FROM
[Sheet1$];

To import into a new table:

SELECT
F1 AS MyCol1,
F3 AS MyCol2,
F5 AS MyCol3
INTO
[Database=C:\MyDB.mdb;].MyExistingTable
FROM
[Sheet1$];
;

Jamie.

 

[Jamie Collins]

I have an access DB that contains
employee information. I need to make a user form in Excel that manipulates
the data in the Access DB. I can handle making the userform and the Access
DB, just wondering if you knew how I would go about connecting and executing
some DML.

I'd use the Execute method of the ADO Connection object. The following
sample code assumes you have set a reference to ADODB (Microsoft
ActiveX Data Objects 2.n Library):

Private Sub btnOK_Click()

' Validation
txtLname.Text = Trim(txtLname.Text)
If Len(txtLname.Text) = 0 Then
MsgBox "No data entered."
Exit Sub
End If
If IsNumeric(txtLname.Text) _
Or IsNumeric(txtLname.Text) Then
MsgBox "Invalid data."
Exit Sub
End If

' Create sql text
Const DML_INSERT As String = "" & _
"INSERT INTO MyTable (lname)" & _
" VALUES ('<<LAST_NAME>>');"

Dim strSql As String
strSql = DML_INSERT
strSql = VBA.Replace(strSql, _
"<<LAST_NAME>>", txtLname.Text)

' Open connection
Const CONN_STRING As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Tempo\New_Jet_DB.mdb"

Dim Con As ADODB.Connection
Set Con = New ADODB.Connection

With Con
.ConnectionString = CONN_STRING
.Open

' Execute sql
Dim lngRowsAffected
.Execute strSql, lngRowsAffected, adCmdText

' Close the connection
.Close

End With

' Handle possible execute failure
If lngRowsAffected <> 1 Then
MsgBox "Could not update the database."
Exit Sub
End If

Me.Hide

End Sub


Jamie.

--