Opening a SQL Server Database using VBA

Do you have to use the OpenDatabase method for a particular reason? I use Connection objects instead, and I can still do recordsets and appends and etc., etc., etc. Example:

    Set con = New ADODB.Connection
    con.ConnectionTimeout = 0
    con.Open "Driver={SQL Server};Server=Holt_SQL; Database=Holt;Trusted_Connection=yes"

would something like that work for you?

Remove the word HOLT at
opendatabase("HOLT", False.....

If you place the word holt there, it will try to look in the workspace holt for the database and not on the SQL Server.

Your connection string also contains the word SERVER=
This is, as far as I know now a property of the opendatabase statement.
Your connection string should look like:
Set db2 = DBEngine.OpenDatabase("", False, False, "ODBC;DSN=HOLT;DATABASE=HOLT")
The server is already known because it is stated in the DSN.

I used the OpenDatabase method because that was the example used in the book Access97 Developer's Handbook.
I tried to type in your suggestion, but when I don't have ADODB as a choice anywhere.  What .dll do I need to reference for this?

-Sasha

pookytoo:

I used the connection string you suggested and that part of it now works.  I am getting a different error now.
It is saying "Couldn't find File C:\WINNIT\Profiles\admin39\Peronnal\dbo.mdb"

admin39 is the logon id.  I noticed, when I opened up the config for the ODBC connection, that it wasn't storing the password which goes along with the login.  I have 'with Windows NT authentication using the network login ID" checked.

so, what can I do about this error?

thanks
Sasha

pookytoo:

I used the connection string you suggested and that part of it now works.  I am getting a different error now.
It is saying "Couldn't find File C:\WINNIT\Profiles\admin39\Peronnal\dbo.mdb"

admin39 is the logon id.  I noticed, when I opened up the config for the ODBC connection, that it wasn't storing the password which goes along with the login.  I have 'with Windows NT authentication using the network login ID" checked.

so, what can I do about this error?

thanks
Sasha

pookytoo:

I used the connection string you suggested and that part of it now works.  I am getting a different error now.
It is saying "Couldn't find File C:\WINNIT\Profiles\admin39\Peronnal\dbo.mdb"

admin39 is the logon id.  I noticed, when I opened up the config for the ODBC connection, that it wasn't storing the password which goes along with the login.  I have 'with Windows NT authentication using the network login ID" checked.

so, what can I do about this error?

thanks
Sasha

sorry for the multiple posts,
having trouble with Iexplorere this am.

SBFurr: as an aside, if IE gives you a problem, don't Reload your browser -- go to the top of the EE screen and click "Reload Question" :o)

If you want to pursue the ADODB angle, go to Tools | References and select ActiveX Data Objects (probably 2.0 or 2.1 is installed on your machine).

You can send the userid and password from within the connection string:

Set db2 = DBEngine.OpenDatabase("", False, False, "ODBC;DSN=HOLT;UID=<username>;PWD=<password>;DATABASE=HOLT")


I don't know what dbo.mdb is.....
Is a a database you created?

thanks,pookytoo, for the assorted advice.
I set the references to include ActiveX and it is working better.  
I get the ADODB.Connection option now.
However, I don't get Con.ConnectionTimeout or Con.Open.
I get con.OpenRecordest and con.Connect.

I'd prefer using ADODB, but it would be useful to learn both approaches.
I don't know what dbo.mdb is, either.  I didn't make it, I can't find it, I don't know what its looking for.
When I link the tables in SQL Server to Access it does prefix their names with dbo_<tablename>.  But I can't find anywhere that I could be asking for it as an .mdb!

thanks,
SBFurr

how did you dimension your connection object? Is it

Dim con as ADODB.Connection?

brewdog:
I had it
Dim con as connection, I changed it to Dim con as ADODB.Connection, and now I am getting the options I need.
How do I set the database variable to the SQL database I'm connecting to?

pookytoo:

after I establish the connection, I want to loop through the tables collection, copying the access tables to SQL.  
the code is:

Set db2 = DBEngine.OpenDatabase("", False, False, "ODBC;DSN=HOLT;DATABASE=HOLT")


'Loop through the table collection in Access.
For Each Tbl In Tbls
'pick up name of current Access table.
strTblname = Tbl.Name
strTblname2 = db2.TableDefs(strTblname).Name 'open the corresponding table in SQL Server & store its name.
strSQL = "Select * Into " & strTblname2 & " From " & strTblname & ";" 'Concantate the SQL string using variables.
DoCmd.RunSQL strSQL 'run the SQL statement.
strTblname = "" 'reset variables
strTblname2 = ""

Next Tbl 'move to the next table in the MS Access tables collection.

the error, where Access looks for dbo.mdb, occurs in the concantating of the SQL string.

ideas?

thanks for all the help so far!

I was able to reproduce your problem here.

I haven't found the answer yet.

I found that a lot of Access users had this problem and that most of the time their solution is to use 'insert into' in stead of 'select into'.

I did not have the time yet to see if that (and how it) works.

That will be on monday........if you can wait so long.

nice to know its a common problem :P
Monday is fine.
I tried the Insert into, but got the same error...
there are plenty of other things I can work on for the remainder of the day.

thanks.
Sasha

There's a couple of ways to handle this:
1)  Treat the SQL Server (HOLT) as tables linked from Access.  Once the tables are linked, you can treat them like local tables in your code.
2)  Don't link the tables.  This method involves using a programmatic connection (Preferably ADO because of the performance) to HOLT and writing SQL Server specific SQL queries to pass the data from the unix tables.

The problem with your OpenDatabase statement is that I don't think you can use that to "open" a sql server database.  You can only open a connection to SQL server.  I'm fairly certain that the OpenDatabase method is for opening only MDB files which is why it can't find it.

Option 1 is probably the best way to go.
If you want to keep them separate, create a new MDB and then link the HOLT tables into your NEW mdb so they show up in the tables tab.  Once they are linked, you can access them just like local tables through the table collection.

In your original MDB, now use the OpenDatabase method to set the database variable to the NEW mdb file.  Now, your original code should work.  Be careful of the select into and insert into.  In sql server, I think the select into actually creates a new table.  So if you already have the table structure in place, use a subquery - something like
insert into mysqltablename (select * from myunixtablename)
or use the QBE grid to build the queries and just run the prebuilt queries from code.

Option 2 involves a little more.  You open a connection using ADO to HOLT.  HOLT is not really treated as a database, more of a phone line to send information down.  SQL Server takes the command/statement and info and does what it needs to do.  Doing it this way avoids using the Access Jet Engine which will improve performance.  But it can be harder to troubleshoot.

Anyway, don't know if I gave you too much or too little.  Hope it helps, but post if I can explain it better.

SnoopDogg changed the proposed answer to a comment

I lied about the opendatabase method.  You CAN use it to open the reference to SQL Server.  But you still have to screw around with connection strings.  Easier to set up a dsn and link the tables to the MDB through the dsn.

I have a DSN set up for the SQL server, and I'd prefer to do all this using ADO because we're dealing with around 200 tables.  
I have the connection working now:

Set con = New ADODB.Connection
con.ConnectionTimeout = 0
con.Open "DSN=HOLT;DATABASE=HOLT", "sa", ""

my current problem with this approach is I can't figure out how to set the object variables in Holt.  
Set db2 = con??????
I tried con.DefaultDatabase but it said 'type mismatch'.

so, how to set the database object and table objects through the connection?

thanks,
this is becoming quite a lengthy discussion.

Sasha

ok,
not sure if this is progress or not.

I tried to set the database object by using the following:

Set con = New ADODB.Connection
con.ConnectionTimeout = 0
con.Open "DSN=HOLT;DATABASE=HOLT", "sa", ""

Set db2 = DBEngine().OpenDatabase("", , , con)


it sets the variable db2, but then when I get to running the SQL statment it gives me the same error.  It's still looking for a .mdb

have you dimmed db2 as an ADODB.Database and then pointed to your SQL Server database with that?

brewdog:
hm, it is dimmed just as a database.  god point.

SnoopDog:
I did make a DTS package to import the tables, but SQL Server told me it was too many tables to do at once, so I set it up to do half.  Unfortunatley, SQL Server is now not reading the Unix.db (Logic).
Ideally I'd have the whole thing run as a DTS package and bypass Access altogether, but I wanted the Access setup working as an alternative.  (I believe in multiple ways of doing things).  

we're tyring to figure out why SQL Server won't talk to Logic this morning.  Access is talking to both SQL Server and Logic, so I ought to be able to use it.


I'll go ahead and change my Dim statement.
thanks,

Sasha

brewdog:
I do not get databae as an option when I try to dim db2 as ADODB.
the closest I get is recordset.

Adjusted points to 175

trying to use ADODB.recordset with the openrecordset method does not work

For Each Tbl In Tbls
'pick up name of current Access table.
strTblname = Tbl.Name
Set Rcrdst2 = db2.OpenRecordset(strTblname)

this results in a type mismatch.

since this is becoming such a mess I'm increasing the points to 175.
whoever can figure out how to set the database variable to the SQL database gets the points :)

Sasha

thanks to this suggestion, I further investigated the DTS straight from the Unix database.  
With a little tweaking, it now works fine, entirely bypassing Access.

I am still curious how to do it in Access, tho.

Sigh. I wish I had more time to investigate stuff like this . . . glad you've got something that works.

hm
I hit "accept comment as answer" but no points were deducted from my acct.

strange....