Solved

Opening a SQL Server Database using VBA

Posted on 2000-03-08
29
807 Views
Last Modified: 2012-08-14
I have an Access database which holds linked tables to a Unix system called Logic.
I need to write a procedure in VBA which takes the linked tables and puts them into the SQL Server database, called HOLT.
If I link tables to the Holt database, Access sees it fine.
However, when I try to use VBA to open the SQL Server database I get an error
"can't find E:\dob.mdb"
the VBA is:

Public db2 As Database
Sub Logic_to_SQL()
'sends logic tables to the SQL Server, Holt datbase.

'sets variable for the SQLServer Database
Set db2 = DBEngine.OpenDatabase("Holt", False, False, "ODBC;DSN=HOLT;SERVER=HOLT_SQL")
(more code....)

Can someone please tell me why it is looking fora .mdb file instead of the SQL server database??

thanks,
Sasha
0
Comment
Question by:SBFurr
  • 17
  • 5
  • 4
  • +1
29 Comments
 
LVL 10

Expert Comment

by:brewdog
ID: 2598181
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?
0
 
LVL 1

Expert Comment

by:pookytoo
ID: 2600513
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.
0
 

Author Comment

by:SBFurr
ID: 2600539
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
0
 

Author Comment

by:SBFurr
ID: 2600579
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
0
 

Author Comment

by:SBFurr
ID: 2600606
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
0
 

Author Comment

by:SBFurr
ID: 2600671
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
0
 

Author Comment

by:SBFurr
ID: 2600675
sorry for the multiple posts,
having trouble with Iexplorere this am.
0
 
LVL 10

Expert Comment

by:brewdog
ID: 2600819
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).
0
 
LVL 1

Expert Comment

by:pookytoo
ID: 2600970
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?
0
 

Author Comment

by:SBFurr
ID: 2601057
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
0
 
LVL 10

Expert Comment

by:brewdog
ID: 2601148
how did you dimension your connection object? Is it

Dim con as ADODB.Connection?
0
 

Author Comment

by:SBFurr
ID: 2604535
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?

0
 

Author Comment

by:SBFurr
ID: 2604575
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!
0
 
LVL 1

Expert Comment

by:pookytoo
ID: 2605215
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.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:SBFurr
ID: 2605418
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
0
 

Expert Comment

by:SnoopDogg
ID: 2605749
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.
0
 

Expert Comment

by:SnoopDogg
ID: 2605757
SnoopDogg changed the proposed answer to a comment
0
 

Expert Comment

by:SnoopDogg
ID: 2605794
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.
0
 

Author Comment

by:SBFurr
ID: 2606089
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
0
 

Author Comment

by:SBFurr
ID: 2606149
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
0
 
LVL 10

Expert Comment

by:brewdog
ID: 2606417
have you dimmed db2 as an ADODB.Database and then pointed to your SQL Server database with that?
0
 

Accepted Solution

by:
SnoopDogg earned 175 total points
ID: 2606738
Question:  Do the table structures already exist within the Holt SQL Server?  If they don't and you have something like 200 tables and you're on SQL Server 7, run the import/export wizard to get at least the table framework in. I don't think there's any way to get around some manual tweaking of the datatypes for some of the fields if they don't map over exactly.

Another question:  Do you need to append records or you just want copies of the tables from the Unix db?

0
 

Author Comment

by:SBFurr
ID: 2612261
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
0
 

Author Comment

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

0
 

Author Comment

by:SBFurr
ID: 2612344
Adjusted points to 175
0
 

Author Comment

by:SBFurr
ID: 2612345
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
0
 

Author Comment

by:SBFurr
ID: 2617220
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.
0
 
LVL 10

Expert Comment

by:brewdog
ID: 2617289
Sigh. I wish I had more time to investigate stuff like this . . . glad you've got something that works.
0
 

Author Comment

by:SBFurr
ID: 2619806
hm
I hit "accept comment as answer" but no points were deducted from my acct.

strange....
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now