insert excel to access

  • Thread starter Thread starter prabhu s
  • Start date Start date
P

prabhu s

Hi..
This is prabu. i have a question. i want to insert excel record
into access(not import or export).

i have text box in excel sheet Name, age, address & i have one button.
once i hit the button. all record insert to access.
can you help me for this.
 
It woulkld be easy to to inf you record a macro while perfroming a databae
query. I can then use the SQL from the read to create the write macro.

1) Start Recording a macro
2) go to worksheet menu

Data - Import External data - new database query

3) retrieve the same records you plan to write.
4) stop recording and post your macro.
 
search this newsgroup - I posted some code for a similar query withing the
past month I'm sure
in the IDE ( the development environment) set a reference (Tools.referemces)
to Microsoft Active Data Objects 2.6 Library

Sub WriteDataToAccess()
Dim MyFile As String
Dim con As New ADODB.Connection
Dim SQL As String
Dim com As ADODB.Command

MyFile = "E:\Excel\Excel_Demos\Risk.mdb"
SQL = "insert into BondTable ( [Currency] ,[Security] ) values
('USD','Trsry 4 2018')"

con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyFile

With New Command
.ActiveConnection = con
.CommandType = adCmdText
.CommandText = SQL
.Execute
Debug.Print .Properties.Count
End With
con.close
Set con = Nothing
End Sub

you need to chaneg the obvious - database name etc, and amend the SQL
statement of course
 
Back
Top