[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2011-03-24
17
Medium Priority
?
400 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 800 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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 800 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 1200 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 1200 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 1200 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

656 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