Connecting to SQL Server thru Access?

[dmann21]

Hey. Can anyone help me connect thru VBA to SQL Server. Im using Access and I'm new to VBA. I know theres something like:
CurrentDB.OpenConnection
and something with ADODB and RecordSets but I don't know anything about it.
I'm trying to update the SQL DB and I was using DoCmd.RunSQL but I have no idea what I'm doing. HELP please.

 

[xlbo]

Depending on your setup, the easiest way may be to link to the table using ODBC - the only catch is if your IT dept have configured the ODBC to be read only.

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[dmann21]

thanx geoff but if u dont mind i need a little more help than that. i dont know what ODBC is?!

 

[xlbo]

erm - if you don't mind me asking - why are you being asked to update a SQL server table then ???
ODBC allows you to connect to different applications (Open DataBase Connectivity)
In access, if you are in the TABLES view
go New>Link
From the drop down box of file types, scroll to the bottom and pick ODBC data sources
There should be an ODBC source set up for your specific SQL server database / datawarehouse. If there are a few, you'll need to speak to your DBA to find out which to use

here is a fairly good definition that I've dragged off the web:

Abbreviation of Open DataBase Connectivity, a standard database access method developed by Microsoft Corporation. The goal of ODBC is to make it possible to access any data from any application, regardless of which database management system (DBMS) is handling the data. ODBC manages this by inserting a middle layer, called a database driver , between an application and the DBMS. The purpose of this layer is to translate the application's data queries into commands that the DBMS understands. For this to work, both the application and the DBMS must be ODBC-compliant -- that is, the application must be capable of issuing ODBC commands and the DBMS must be capable of responding to them. Since version 2.0, the standard supports SAG SQL.

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244