Hi Tekmaven,

First of all, I don't see the file type "Access database" in my Excel Worksheet.

Secondly, I don't want to access my database manually like you propose File/Open....etc.. I need to program it by clicking on a button, the user can recuperate datas in a table and display them on the Excel worksheet.

Then why are you using Excel? Why not just use Access? You can accomplish this by creating a "Form".

Hi Tekmaven,

Because I have a lots of calculation in my project and Excel is a good tool and easy way for me to do it :p)

Well, if you want to synchronize Access information from an Excel sheet (Which is what I think you want to do), you don't need any code. Synchronizing it with a sheet automatically sounds like a much better way to
go rather than a button that does it (unless you want to do call a vbscript, but it's not necessary in this case). A user can just go to the sheet and it'll be there. I've done this before, but not in Excel 7.0.

If you want to synchronize a sheet in your workbook with output from an ODBC datasource, go to the menu Data -> Get External Data -> New Database Query. If you don't have Microsoft Query, you might be asked to insert the CD and install this feature. If it all goes well, you should get a window where you get to pick a data source. Choose one you created and click ok. After that, you'll get another window where you can pick what columns you want. Select what columns and click on the -> button. Click next. The following dialogs are
self explanatory (sort by, etc.). The last dialog (Query Wizard - Finish) you can select "Return Data to Microsoft Excel." You can save the query if you want to by pressing on the button "Save Query." Click on finish.

After you do all that, you'll get a dialog that says "Returning External Data to Microsoft Excel." Select existing worksheet (assuming it's blank). Click ok. The information will be imported to your current sheet. The data is dynamic however. Any change you do in your ODBC database, it will update in Excel automatically. You can right click and hit Refresh. If you want you can right click on one of the fields and go to External Data Range Properties. There you can control the Refresh rate.

I hope what you want is NOT a client in Excel to insert information into an Access database. There are better solutions than trying to do this, but something like that can be done.

This is probably a little too late for your project, but here is the answer (that was a pain to hack):

Set DB = WS.OpenDatabase("", False, False, "DSN=BLAH_NAME;SRVR=TCP/IP:blah_host.com:BLAH_SID;UID=BLAH_USER;PWD=BLAH_PASSWORD")

The false, false are what makes this work.

Hope that helps

Hmm, I am curious, what version of Access are you trying to connect to? You say you are using Excel 97, but what version of Access? And why are you using DAO to connect via ODBC instead of ADO ?

ADO would have made you life a whole lot simplier.

I am new to VB.Net and have a question. I am trying to connect VB.Net to Access. What I want to do is , when i select the database name, I want to popup the tables in the database...when one of the table is seleted, then the fields in the table should pop up...Can someone tell me how to go about it .

thanks in advance

Vihang

I’ve got a problem with this program.It only saves one data at time.What shall I do with it?
Private Sub cmdsave_Click()


Adodcprog.Recordset.MoveNext


And with this program I couldn’t search a data I wanted.Pls somebody help me!
End Sub
Private Sub cmdsearch_Click()
Dim stremployeeName As String
Dim strpaysheet As Variant
stremployeeName = InputBox("Please enter empid")
strpaysheet = datPrimaryRS.Recordset.Paysheet
datPrimaryRS.Recordset.Find "CompanyName Like " & _
Chr(39) & stremployeeName & "*" & Chr(39)
If datPrimaryRS.Recordset.EOF Then
datPrimaryRS.Recordset.Paysheet = strpaysheet
MsgBox stremployeeName & " not found."
End If
End Sub

Check this out: Visual Basic Database Guide. It explains in detail how to connect to different databases (including Microsoft Access, SQL, Oracle). I think it should help you get a great understanding of VB and data access.