Solved

In MS-Access 2007 create database and table on SQLEXPRESS 2008

Posted on 2011-03-24
17
350 Views
Last Modified: 2012-05-11
In my workgroup my Sql Server 2008 is "TED\SQLEXPRESS" where user "Frank" has all the necessary privlidges, In Access 2007 I would like to check if database "MyDB" exists, and if not I would like to create this DB.  I do not want to use a DSN, and would like to use only DAO protocols. I need help with code to establish this DSN-less connection, then maybe I can figure out the rest.
Thanks for your help,
Frank
0
Comment
Question by:FrankBabz
  • 8
  • 5
  • 4
17 Comments
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 35212768
You can just connect specifying "master" as your database, I believe - everybody has guest rights to that database, so everybody on the server should be able to connect. Once you're connected, you can do a:

SELECT * FROM sysdatabases
where name = 'YourDBName

Open in new window

'

To see if the database exists. You'll be able to see all the databases on the server, even if you don't have the rights to login to them, as long as you haven't revoked their "Guest" access. If you've revoked "Guest" access, they'll disappear from that query (unless you have access to them). In the case of your user, it shouldn't matter because you're saying he has the access he needs.

One you've confirmed that it doesn't exist, you can create the database using the ADO SQL command:

sql = "CREATE DATABASE " & new_database_name 
conn.execute sql, , 129  'Where "conn" is the database connection you've already created

Open in new window


You'll need to make sure your user "Frank" is in the db_Creator role on the server as well, or he won't have rights to create the database.
0
 

Author Comment

by:FrankBabz
ID: 35214910
Thanks ryan,
All you said is great.....   however....
What I tried to ask for, and really need, is the first few lines of code to create and establish that "conn" connection.  
0
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 200 total points
ID: 35215457
I got these from http://www.codemaker.co.uk/it/tips/ado_conn.htm#OLEDBProviderForSQLServer, and they're all DSN-less:

]Dim oConn
Set oConn = Server.CreateObject("ADODB.Connection")

' For Standard Security
oConn.Open "Provider=sqloledb;" & _ 
           "Data Source=myServerName;" & _
           "Initial Catalog=myDatabaseName;" & _
           "User Id=myUsername;" & _
           "Password=myPassword"
 
' For a Trusted Connection
oConn.Open "Provider=sqloledb;" & _
           "Data Source=myServerName;" & _
           "Initial Catalog=myDatabaseName;" & _
           "Integrated Security=SSPI"
 
' To connect to a "Named Instance"
oConn.Open "Provider=sqloledb;" & _
           "Data Source=myServerName\myInstanceName;" & _
           "Initial Catalog=myDatabaseName;" & _
           "User Id=myUsername;" & _
           "Password=myPassword"

Open in new window


0
 

Author Comment

by:FrankBabz
ID: 35217367
Thanks again Ryan,
In line 2 the access compiler says "server" is not defined.  Do I need a reference?
I appreciate your help and the above data and link.
Frank
0
 

Author Comment

by:FrankBabz
ID: 35217507
Some research suggested dropping "Server." and I think this worked:
Set oConn = CreateObject("ADODB.Connection")
....Let me do some more testing....
Frank
0
 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 200 total points
ID: 35217668
Hmm - that code seems to work fine in classic ASP, but maybe Access doesn't have access to the Server object:

http://support.microsoft.com/kb/300382 (the ASP support article from MSFT)

You can try replacing that first pair of statements with:

Dim CN As ADODB.Connection
Set CN = New ADODB.Connection

Open in new window


To use the ADODB namespace, you'll need to add a reference to "Microsoft ActiveX Data Objects X.XX" to your project (where X.XX is the version number of ADO - whatever is installed on your box and will also be installed for others running this database). Once you check that reference, you should be able to create an ADODB.Connection objects and get things rolling.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35217691
They are not using ADO, they are using DAO:
 "I do not want to use a DSN, and would like to use only DAO protocols."
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 35217831
I'm less familiar with DAO, but can I ask - why the commitment to DAO instead of ADO? Are there things you need you connection to do that ADO doesn't support and DAO does?
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:FrankBabz
ID: 35218462
acperkins:  Welcome aboard!    Your comment was accurate.

Ryan:  I am unable to technically answer your DAO v ADO question.  All code in my Access Ap is DAO and I have a strong desire not to mix DAO and ADO as it adds a level of incompatibility with existing knowledge, functions and subroutines.

Follow up to share and discuss....
So far...I only tested  ' For a Trusted Connection:
> It appears "Server." should not be used because apparently VBA does not have another option.
> "Initial Catalog=myDatabaseName;"  is optional and probably defaults to "master"???
> If "Initial Catalog" is used and "myDatabaseName" does not exist.... Err,Number = -2147467259

Question: Having created an ADODB connection, I am unclear how to now incorporate/assign this connection to a DAO.Database variable.  Can you help?  Should I have created a DAO connection to begin with?  Actually, I assumed that was where Ryan was leading me.   Maybe not???
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 300 total points
ID: 35220389
>>Having created an ADODB connection, I am unclear how to now incorporate/assign this connection to a DAO.Database variable. <<
You cannot.  They are different animals.

I am afraid the last time I used DAO was circa 1998 and I cannot help you more than that.  Having said that there should be no problem in using ADO to open a connection to SQL Server.
0
 

Author Comment

by:FrankBabz
ID: 35220561
You both seem to favor ADO v DAO, and I did OK with ADO in ASP, so maybe it is time for me to get with the program and use ADO in Access.  To continue with DSN-less DAO (I think) would require me to use tabledefs and create linked tables that I would need to create/manage.... and I don't particularly want to use that approach, and overhead, when dealing with many SQL tables for brief periods.

I can reference the Microsoft ActiveX Data Objects 6.0 library.  There is also a  Microsoft ActiveX Data Objects Recordset 6.0 library,   Should I include both?  Are these pretty current?  Anything else you would suggest in this regard?  Thanks for your help....

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35223188
>>You both seem to favor ADO v DAO, and I did OK with ADO in ASP, so maybe it is time for me to get with the program and use ADO in Access. <<
There are some that will argue that you should use DAO if you are using an MS Access database as it is faster.  I do not agree with that, as it usually signs of lousy code or worse still trying to use the same bad practices they used with DAO migrated to ADO.  But if you are in fact using a SQL Server database than even the skeptics will agree that ADO is the way to go.  Try to be economical in your use of connections to SQL Server and you will not be disappointed.  That means if you need some data use a Forward-Only, Read-Only recordset.  If you need to Insert, Update or Delete data use a Command object with a SQL INSERT, UPDATE or DELETE statement.

>>Should I include both?<<
No.  Do not use the ADO Recordset library, you do not need it.
0
 

Author Comment

by:FrankBabz
ID: 35224575
Until now I had a simple software game plan/paradigm.  ADO.net was for web site work, and DAO was for Access mdb/accdb work and I never before considered comingling until this thread.  

My current Access Ap (maybe best-called my accdb AP) uses linked tables to other store venues:  mdb, accdb, and yes even several SQL databases. I define the table linkages via DSN's and they are all smoothly "connected" architecturally by the Access engine and are ready for DAO. I have not encountered any notable problems using or manipulating data from any of these stores using DAO.

I now have the need to infrequently access more SQL tables for brief periods of manipulation.  It bothered me to consider my DSN approach because the list of linked tables would be huge, and presumedly at the expense of performance.  I thought "Why not just "connect" when needed, and disconnect when done."  After all, that's how I would do it in ADO.net.   I assumed that would be easy to do in DAO, but could not figure out how to do it, and asked you experts for help.  With your help, I've concluded this "connecting" approach is not possible (with DAO).  

However, DAO "connecting" can be done (I think) using tabledef's to Link tables when needed, and DeLink them when done. While that seems expensive, the beauty of this approach is that it maintains my paradigm, and quite frankly seems to be architecturally accdb-consistant (i.e. it does not require a paradigm changing  reference).

I have an unexplainable instinct that says the future of ADO will focus on .net and the future of DAO will focus on the accdb and that comingling while workable, and clever, may be more difficult to maintain in the future.  Anyway, I'm now leaning toward the tabledef approach, and wanted to share with you my reasoning.

Your opinion and comments are most welcomed.

0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 300 total points
ID: 35224728
>>ADO.net was for web site work, and DAO was for Access mdb/accdb work and I never before considered comingling until this thread.  <<
We were not talking about ADO.NET, but rather ADO.  Two very different animals that just happen to have similar names.  To my knowledge you cannot use ADO.NET in an MS Access app.

>>However, DAO "connecting" can be done (I think) using tabledef's to Link tables when needed<<
I know it can.  I just don't know how.  DAO was dicontinued over 13 years ago.

>>I have an unexplainable instinct that says the future of ADO will focus on .net and the future of DAO will focus on the accdb <<
Both technologies have long since been deprecated.
0
 

Author Comment

by:FrankBabz
ID: 35226398
>>DAO was dicontinued over 13 years ago<<
DAO according to Frank......
Upon installing Access 2007, certain references are "automatically" included by MS for the accdb environment.  In that "default" environment I can "Dim X as DAO.Database"  without need for me to reference any other libraries.  Of further interest none of the DAO reference libraries are checked.  In this default environment. A "DAO.recordset" can be defined, and "X.OpenRecodset" provides complete intellisense.  My conclusion is/was that the Access 12.0 Object libraries coupled with the 12.0 Access database engine library Intrinsicallyprovides as a "default" a newer "DAO" and (hopefully) functionally improved superset of the DAO you used in 1998. Maybe DAO has been resurrected???

I will try the tabledef approach to link a SQL table, and I will post back results.

Thanks again for your assistance.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 300 total points
ID: 35226966
>>certain references are "automatically" included by MS for the accdb environment.<<
That is correct, with MS Access DAO is added by default.

>>Maybe DAO has been resurrected???<<
I am afraid it has not changed and is still dead in the water.
0
 

Author Comment

by:FrankBabz
ID: 35233310
Here is my DAO solution for Link & LinkDelete for anyone it may help....
All my connections are "Trusted"....  Easy to fix if you need Username and Password option

Function TestSQL()
  Dim Er As String, Serv As String, svDB As String, svTable As String, LocalTable As String
  Serv = "TED\SQLEXPRESS"
  svDB = "Concert"
  svTable = "dbo.OwnerDoc"
  LocalTable = "aaaOwnerDoc"
  
  SqlTrustedTableLink LocalTable, Serv, svDB, svTable, Er
  Application.RefreshDatabaseWindow   ' If you want to see that LocalTable was added
  If Er <> "" Then MsgBox Er
  '
  If IsTableNameAttached(LocalTable) Then MsgBox "Is Attached '" & LocalTable & "'" Else MsgBox "Not Attached"
  If Err.number > 0 Then Err.Clear: MsgBox Err.Description  '  Caller handles Err
  '
  SqlTableLinkDelete LocalTable, Er
  Application.RefreshDatabaseWindow
  If Er <> "" Then MsgBox Er
End Function
'
'  ............The 3 basic routines follow........................................
'
Public Sub SqlTrustedTableLink(LocalTableName As String, Server As String, _
                  svDatabase As String, svTable As String, Er As String)
  On Error GoTo Err_SqlTrustedTableLink
  Dim Tdf As TableDef, stConnect As String
  Er = ""
  stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & Server & _
              ";DATABASE=" & svDatabase & ";Trusted_Connection=Yes;"
  Set Tdf = CurrentDb.CreateTableDef(LocalTableName, 0, svTable, stConnect)  ' Attrib. option: dbAttachSavePWD
  CurrentDb.TableDefs.Append Tdf
Exit_SqlTrustedTableLink:
  Exit Sub
Err_SqlTrustedTableLink:
  If Err.number = 3010 Then MsgBox "SqlTrustedTableLink: FYI the table '" & LocalTableName & "' was already attached.": Resume Exit_SqlTrustedTableLink
  Er = "SqlTrustedTableLinkError: " & Err.Description & "," & Err.number
  Resume Exit_SqlTrustedTableLink
End Sub

Public Function IsTableNameAttached(LocalTableName As String) As Boolean
  Dim Tdf As TableDef     ' Caller to handle possible error
  For Each Tdf In CurrentDb.TableDefs
    If Tdf.Name = LocalTableName Then IsTableNameAttached = True: Exit For
  Next
End Function

Public Sub SqlTableLinkDelete(LocalTableName As String, Er As String)
  Dim Tdf As TableDef
  On Error GoTo Err_SqlTableLinkDelete
  Er = ""
  If IsTableNameAttached(LocalTableName) Then CurrentDb.TableDefs.Delete LocalTableName Else MsgBox "SqlTableLinkDelete: FYI table' & LocalTableName & " ' did not exist to delete."
Exit_SqlTableLinkDelete:
  Exit Sub
Err_SqlTableLinkDelete:
  Er = "SqlTableLinkDeleteErr: " & Err.Description & "," & Err.number
  Resume Exit_SqlTableLinkDelete
End Sub

Open in new window


My limited testing seems have everything OK.  Comments/improvements appreciated....
Frank
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

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…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

707 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

14 Experts available now in Live!

Get 1:1 Help Now