Link to home
Start Free TrialLog in
Avatar of K-9
K-9

asked on

Connect to SQL from Access

I have an Access 2000 DB, that I upsized to  SQL 7.0  In my code I have references opening up tables in the access DB.  I need to now convert that code to open up the table from SQL.  

something like:
Dim rstINCHIST As Recordset
Set rstINCHIST = CurrentDB.OpenRecordset("INCHIST", dbOpenTable)
 

I need to now open the DB on my SQL Server named "MASTER"  The DB on that SQL Server is called "MAINSPISSQL"

This should be an easy one for you guys, im just now getting into SQL Server.

Thanks
Avatar of dgorin
dgorin

If you are staying within a MDB file with your code, you could just link all the tables you need and give the linked tables the original name.  Your code should run unaltered.

If your code will be used outside the existing Access MDB, I'd start using ADO instead of DAO.
By creating a DSN data source name, you can connect to that and away you go.  simply create a dsn that points to the SQL database and in the MDB you can use the opendatabase method to access the dsn.

for further instruction see the opendatabase method in access and it will explain the connection string.

 
Avatar of K-9

ASKER

dgorin, that seems to work when I run queries from combo boxes, but not from within the VBA code.  I need to tell it to open the database from SQL Server, not table from the .mdb file.  I will be using the .mdb file as the front end, ie. basically forms.  All the data will be in the SQL end.  

I looked at the opendatabse method, but they really dont go into detail how to connect to an sql server & pull up the table through code.  I need this, to add data, through VBA code, etc..
K9,  in access help, type "open" the first thing that pops up is "open database connectivity odbc" click enter and 3 more things will be available.  All to do with odbc and SQL connectivity.

next type in opendatabase.  this is the syntax;
Set database = workspace.OpenDatabase (dbname, options, read-only, connect)
connection string:
"ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"

hope this helps.




You might look at using an Access Data Project instead of a MDB file.  It is a more efficient front-end, designed as a client-server application.  I'm not sure if you can implement DAO code in a project, I've never tried.
Avatar of K-9

ASKER

SE.. no luck w/ the help file :(

If you could just give me example code, I could take it from there:

The server name is:  Master
DB Name is:  MAINSPISSQL

Say, for example I want to open up a table on mainspissql, and insert a record.

If you could give me actual code, that should do the trick..

Thanks
Avatar of K-9

ASKER

Oops..  table name would be:  Incidents
If that's all you want to do then use a passthrough query.
or an update query.  create an update query, there will be a dialog that opens up giving you the option of the current database or other.  Select other, and go to the location of where the database is and then select the table from within that database.  I can't be more specic than that.  If anyone else in this question nows of a better way to explain it please do.

Also the opendatabase method that I posted is also a good way to do it.  I recomend reading the help files and trying to understand what is going on.  between the help files and the suggestions we are making I hope the light will go on.  I've been in the same position and only through trial and errors and lots of playing around with things have I got things working and picked up a few neat tricks in the process.

I can't really send you specific code, cause the code will be different from what you may have to do.  there are several suggestions and possibilities given already that should be able guide you in the right direction.

 
ASKER CERTIFIED SOLUTION
Avatar of devtha
devtha
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of K-9

ASKER

Thanks all...  You got me on the right track..  Appreciate it :)