Excel to access

stevehorton09 · Sep 13th 2004

`hi all,

i was wondering about having a userform from excel with its data feeding into an access table. Is that possible? I expect the answer is yes but......

at present my userform enters simply into a spreadsheet behind it

i.e textbox1 = a1 etc.

I have unfortunately cant attach my current workbook but if you email at [email protected] i will send it to you and if anyone can tell me how this can be done i would be most appreciative

I have specified the current code below aswell if that helps.
I have really only used access back in my uni days so i need some help here!

If anyone can help me, i have a spare google acccount or two! (gmail).
thanks in advance

steve

Worksheets("Sheet4").Range("b13").Value = combobox1_input
Worksheets("Sheet4").Range("b14").Value = combobox2_input
Worksheets("Sheet4").Range("b15").Value = LocalAccount1.Text
Worksheets("Sheet4").Range("b16").Value = Description1.Text
Unload Me
Sheets("Sheet4").Select

XL-Dennis · Sep 13th 2004

Hi Steve,

Quote

I have attached my current workbook and if anyone can tell me how this can be done i would be most appreciative

Seems that it didn't get through?

stevehorton09 · Sep 13th 2004

can i send it through email to you, it is 175kb and you can only upload 45kb's!

XL-Dennis · Sep 13th 2004

Check You PM-box to find my e-mailaddress.

stevehorton09 · Sep 13th 2004

i have sent it to you......thanks again

XL-Dennis · Sep 13th 2004

Steve,

Below You find the solution to update a Access-database based on You case:

[vba]
'Following variables must be added to the Procedure Commandbutton4_Click
'Add a reference to the Microsoft ActiveX Data 2.5 or later Object Library
'via the Tool | References... in the VB-editor
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stDB As String
Dim stCon As String
Dim i As Long

'Instantiate the ADO COM's objects.
Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset

'Pathway and name of the database
stDB = ThisWorkbook.Path & "\" & "XLData.mdb"

'Create the connectionstring.
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & stDB & ";"

'Open the connection
cnt.Open stCon

'Open the recordset and You need to change to the
'correct table name.
rst.Open "tableName", cnt, 1, 3, adCmdTableDirect

'Add the new record and You need to change the name
'of the fields.
With rst
.AddNew
.Fields("Name") = combobox1_input
.Fields("House") = combobox2_input
.Fields("Third") = LocalAccount1.Text
.Fields("Fourth") = Description1.Text
.Update
End With

'Close the recordset and close the connection.
rst.Close
cnt.Close

'Release objects from memory.
Set rst = Nothing
Set cnt = Nothing

Unload Me

'You can delete the following lines.
'Worksheets("Sheet4").Range("b13").Value = combobox1_input
'Worksheets("Sheet4").Range("b14").Value = combobox2_input
'Worksheets("Sheet4").Range("b15").Value = LocalAccount1.Text
'Worksheets("Sheet4").Range("b16").Value = Description1.Text
[/VBA]

Pls test it and mail back to the forum about the outcome. I'm off to bed now but will read it tomorrow my local time

stevehorton09 · Sep 14th 2004

Still not sure about this.....

Hi im getting the following error, wrong number of arguements or invalid property assignment

any ideas below is the code im using

Private Sub CommandButton4_Click()
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stDB As String
Dim stCon As String
Dim i As Long

'Instantiate the ADO COM's objects.
Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset

'Pathway and name of the database
stDB = ThisWorkbook.Path & "\" & "supplier.mdb"

'Create the connectionstring.
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & stDB & ";"

'Open the connection
cnt.Open stCon

'Open the recordset and You need to change to the
'correct table name.
rst.Open "customers", cnt, 1, 3, adCmdTableDirect, _
adLockReadOnly, adCmdTableDirect

'Add the new record and You need to change the name
'of the fields.
With rst
.AddNew
.Fields("Company Name") = combobox1_input

.Update
End With

'Close the recordset and close the connection.
rst.Close
cnt.Close

'Release objects from memory.
Set rst = Nothing
Set cnt = Nothing

Unload Me
End Sub

gollem · Sep 15th 2004

Hi,

which line gives the error(when you do debug)?

Gollem

XL-Dennis · Sep 15th 2004

Steve,

This line should be replaced:
[vba]
rst.Open "customers", cnt, 1, 3, adCmdTableDirect, _
adLockReadOnly, adCmdTableDirect
[/vba]

With:
[vba]
rst.Open "customers", cnt, 1, 3, adCmdTableDirect
[/VBA]

stevehorton09 · Sep 15th 2004

morning,

i have made the change but it is saying items cannots be found in collectios corresponding to the requested name or ordinal and when i push debug, it shows this line

.fiels("Company name") = localaccount1

does anyone know why?

gollem · Sep 15th 2004

Hi,

check if "Company name" is exactly the name of the field.
It has to be exactly the same(capitals,spaces?)....

Gollem

stevehorton09 · Sep 15th 2004

hi gollem, after going into design view in access i realised you can not have spaces in the field name!

So problem solved!

the simple things a!

thanks

XL-Dennis · Sep 15th 2004

Steve,

Two things in addition to gollem latest post:

#1 Does the variable localaccount1 hold any value?

#2 In Your post it says: ".fiels" while the correct one should be .Fields

stevehorton09 · Sep 15th 2004

sorry simply a typing error.

It does hold values and works perfectly.

Ok so now i can enter info into excel and then it is input into an access table, which is great.

Is it possible for that data to then be queried through excel. I.e i wanted to find local account 123 so i would click a button enter the local account and then it would download the record to say sheet 1 or i could download all records to sheet 1?

gollem · Sep 15th 2004

Hi,

you can use the original code of XlDennis(same beginnin and ending).
Just replace the code in the middle:

Code

rst.Open "select * customer where account=" & 123, cnt, adOpenDynamic, adLockOptimistic
Do While rst.EOF = False
    ActiveSheet.Range("A1").Value = rst.Fields("account").Value
    
    rst.MoveNext
Loop

* = select every field in the table
Customer = your table
account = the field you want to search

Hope this example gives you an idea.

Gollem

sal21 · Oct 29th 2004

Hi Dennis i have a similar prob of stevehorton09 and i dont solve it self...
Do you like to help me.
there is my project in excel and mdb...

http:/www.gssitaly.com/l0785_def.zip
http:/www.gssitaly.com/prova.zip

In effect i would want up date the sheet and automaticlly during the inop data from the userform also the Table TOTALE, is possible...

The free input txt box are marked in the images tks in advance....
http://HTTP://www.gssitaly.com/txtbox.gif
Sorry but this is my first work on access and excel
Sal.

Excel to access

Participate now!

Share

Users Viewing This Thread