Solved

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

Posted on 2011-03-24
17
370 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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
 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

776 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