Solved

Create connection string from Access to SQL Server sitting on a VMWare virtual machine

Posted on 2016-07-25
21
88 Views
Last Modified: 2016-10-03
I have SQL Server 2014 sitting on a VM on my desktop.  That particular VM also has Office 2003 installed and I do not want to complicate manners by installing Access 2013 on that VM.

So what I need to do is create a linked table on my primary computer which is linked to the SQL Server on the VM, with a DSN-less connection.

Any help would be greatly appreciated.
0
Comment
Question by:Dale Fye (Access MVP)
  • 7
  • 4
  • 4
  • +2
21 Comments
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 41727598
Oh, BTW, both the primary computer are running Windows 7 Pro
0
 
LVL 32

Expert Comment

by:ste5an
ID: 41728935
The architecture using VMs does not matter. You need first to ensure that you can reach that VM. So can you ping it?

Then you just need to connect it:

Public Function TableLinkSqlAsTable(ASourceDatabase As String, ASourceSchema As String, ASourceName As String, ADestinationName As String, APrimaryKeyFields As String) As Boolean
    
  Const CONNECTION_STRING As String = "ODBC;Driver={SQL Server Native Client 1{0}.0};Server={1};Database={2};Trusted_Connection=yes;"
  
  Const SQL_CREATE_INDEX As String = "CREATE INDEX PK_{0} ON {0} ( {1} ) WITH PRIMARY;"
  Const SQL_DROP_INDEX As String = "DROP INDEX {0} ON {1};"
  
  On Local Error GoTo LocalError
     
  Dim Index As DAO.Index
  Dim TableDef As DAO.TableDef
 
  Dim Count As Integer
  
  TableLinkSqlAsTable = False
  ASourceName = ASourceName
  If ADestinationName = "" Then
    ADestinationName = ASourceName
  Else
    ADestinationName = ADestinationName
  End If
         
  QueryDelete ADestinationName
  TableDelete ADestinationName
  Set TableDef = CurrentDbC.CreateTableDef(ADestinationName)
  TableDef.Connect = FormatStr(CONNECTION_STRING, "0", GetServerIP, ASourceDatabase)
  TableDef.SourceTableName = FormatStr("{0}.{1}", ASourceSchema, ASourceName)
  
  On Local Error Resume Next
  
  CurrentDbC.TableDefs.Append TableDef
  If Err.Number <> 0 Then
    On Local Error GoTo LocalError
    TableDef.Connect = FormatStr(CONNECTION_STRING, "1", GetServerIP, ASourceDatabase)
    CurrentDbC.TableDefs.Append TableDef
  End If
  
  On Local Error GoTo LocalError
   
  If APrimaryKeyFields <> "" Then
    For Each Index In TableDef.Indexes
      If Index.Primary Then
        SqlExecuteFmt SQL_DROP_INDEX, Index.Name, ADestinationName
        Exit For
      End If
    Next Index
     
    SqlExecuteFmt SQL_CREATE_INDEX, ADestinationName, APrimaryKeyFields
  End If
    
  CurrentDbC.TableDefs.Refresh
  Set Index = Nothing
  Set TableDef = Nothing
  TableLinkSqlAsTable = True
  Exit Function
  
LocalError:
  DebugError "TableLinkSqlAsTable", ASourceName
  
End Function

Open in new window


with

Public Function FormatStr(AString As String, ParamArray AValues() As Variant) As String

  Dim Count As Long
  Dim Result As String
  
  Result = AString
  
  For Count = 0 To UBound(AValues())
    Result = Replace(Result, "{" & Count & "}", Nz(AValues(Count), "NULL"))
  Next Count
  
  Result = Replace(Result, "{CRLF}", vbCrLf)
  FormatStr = Result

End Function

Public Function SqlExecuteFmt(AStatement As String, ParamArray AValues() As Variant) As Boolean

  Dim Count As Long
  Dim Result As String
  Dim Statement As String

  Statement = AStatement
  For Count = 0 To UBound(AValues())
    Statement = Replace(Statement, "{" & Count & "}", Nz(AValues(Count), "NULL"))
  Next Count

  SqlExecuteFmt = SqlExecute(Statement)

End Function

Public Function SqlExecute(AStatement As String, Optional ASilent As Boolean, Optional ARecordsAffected As Long) As Boolean

  On Local Error GoTo LocalError

  Dim OldMousePointer  As Long

  OldMousePointer = Screen.MousePointer
  Screen.MousePointer = ccHourglass
  SqlExecute = False
  CurrentDbC.Execute AStatement, dbFailOnError Or dbSeeChanges
  ARecordsAffected = CurrentDbC.RecordsAffected
  SqlExecute = True
  Screen.MousePointer = OldMousePointer
  Exit Function

LocalError:
  Screen.MousePointer = OldMousePointer
  Debug.Print ""&H" & Hex(Err.Number); Err.Description; "SQL: " & AStatement
  If Not ASilent Then
     MsgBox(Err.Description)        
  End If

End Function

Open in new window


And I guess you can figure out the outer helper methods..
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 41729262
@ste5an,

I guess that is my issue, I'm not sure how to ping that VM.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 41729284
Just try?! Of course you need to be able to access the VM prior to doing anything else.
0
 
LVL 57
ID: 41729318
Dale,

 Go into the VM.

 Drop to a command prompt and type:

ipconfig /all

 You'll see a IP Address listed under the VM NIC.   Use that.   To verify, type:

 Ping <ip address>

 You should get replies.

 Then type SET with a return.   Look for "HOSTNAME"

Then try:

 Ping <host name>

You should get reply's back.  If not, DNS is messed up, but you will be able to use the IP.   If that works, go to the host machine, open a command prompt and do the same thing.

 If it's working there, your set, just use that host name in the connection string.

Jim.
0
 
LVL 32

Expert Comment

by:ste5an
ID: 41729368
hmm, it's some time, that I've used VMware..  but the when using VMware Workstation then the necessary virtual switch is created automatically.

So you only need to check, whether you VM guest has a static IP ( ipconfig /all ) or an resolvable name (depending on your home router and DHCP).
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 41729606
@Jim

Got the IP and the Ping worked, but no "HostName" listed after issuing the SET command.

But I'll try it with the IP address.
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 41729608
Pinging the IP address from the host did not work.  "Request timed out"
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 41729673
The Windows Firewall service of the VM might block inbound traffic. In Start Menu, type "Advanced Firewall", then call the applet displayed, and select "Properties" from the context menu inside of the applet. Then make sure the firewall is either off or not blocking inbound traffic (default is to block everything not explicitely allowed by rules).
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 500 total points
ID: 41729855
1.  Link the table by using regular way, i.e. by using "link table" wizard via ODBC, and create new DSN entry. The process is described in this article:

https://support.office.com/en-us/article/Import-or-link-to-SQL-Server-data-a5a3b4eb-57b9-45a0-b732-77bc6089b84e

under "Link to sql server data". The name of sql server machine is the network name of your V.M.

2. Once you have linked the table, verify that you can see the actual data.

3. In fact, this link is already DSN-less (you can now remove DSN entry you created at step 1, the linked table will still work), but you can adjust it if you want. For that, open the linked table in design view, then right-click, select "Properties", and adjust the value in "Description". If this Access database is supposed to be used on other computers, it makes sense to remove WSID, and maybe adjust APP to the specific project, for better recognition. No coding is required.

In order to step 1 to succeed, you need to

1. ensure that your v.m. is configured with network adapter and there's network connection between your host and v.m - here's a video:
https://www.youtube.com/watch?v=-1_lSAmLerM

2. disable firewall on virtual machine

3. ensure that sql server on v.m. allows remote access:
https://technet.microsoft.com/en-us/library/ms156468(v=sql.105).aspx
https://blogs.msdn.microsoft.com/sql_protocols/2008/04/30/steps-to-troubleshoot-sql-connectivity-issues/
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 41729877
> but no "HostName" listed after issuing the SET command.

Look for COMPUTERNAME.
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 41729906
@Vadim,

Found "ComputerName", interesting that it changed the "_" in the original to a "-", but still returned an error on the Ping.  My guess is that it has to do with the network adapter and/or firewall mentioned in your previous post.  Will have to check that later today.

Dale
0
 
LVL 57
ID: 41730158
You can as a work around put an entry in your HOSTs file for the VM.

It's in:

C:\Windows\System32\drivers\etc

It's a text file.  Edit it and on the last line type the computer name followed by the IP address.

Save it, then test with a Ping by computername.

Past that, your down to firewall and/or SQL Server settings.

Jim.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 41731138
Underscores are not "allowed" in DNS names officially, so they are changed to dashes if used in a NetBIOS name. A NetBIOS name wasn't allowed to contain a dash in the (very) past, so some translation back and forth took place, and may because of compatibility settings.
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 41736827
still working on this guys, don't close the question
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 41825598
With due respect, I object to awarding the points to the answers that suggested writing code, because the code is totally unnecessary. The question was how to create dsn-less connection from Access to remote sql server. Assigning points creates an impression that this can only be achieved programmatically, which is not so. The very short answer is "create them as usual", in the 100% regular way described in Access Help, and in Microsoft article I cited in my comment.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 41826194
Vadim,

whether code is used or the manual setup you describe is not your decision. Without considering the OP background (as far as we can see from this thread), and with no response by the OP, both answers are equally useful. Looking at the level of communication your way is slightly more appliable, IMHO.

My suggestion is to just accept exactly those two comments, https:#a41728935 (Assist) and https:#a41729855 (Best).
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 41826243
My point is exactly that writing code for something that can be achieved by standard, simple, and documented means, is not reasonable - unless there was specific request to write the code, such as to make the operation of linking the table reusable by a single button click, or to make it fully automatic within an application. Compare to a question "how to make the text bold in Word?", where the suggestion would be to write the code, rather than to press ctrl-B or to click the button in toolbar.
0
 
LVL 47

Author Closing Comment

by:Dale Fye (Access MVP)
ID: 41826268
Sorry, guys, this fell off my radar.  Hope this resolves the issue.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

HOW TO: Upload an ISO image to a VMware datastore for use with VMware vSphere Hypervisor 6.5 (ESXi 6.5) using the vSphere Host Client, and checking its MD5 checksum signature is correct.  It's a good idea to compare checksums, because many installat…
In this article, I show you step by step with screenshots to assist you - HOW TO: Deploy and Install the VMware vCenter Server Appliance 6.5 (VCSA 6.5), with some helpful tips along the way.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

747 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

9 Experts available now in Live!

Get 1:1 Help Now