Dale Fye
asked on
Create connection string from Access to SQL Server sitting on a VMWare virtual machine
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.
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.
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:
with
And I guess you can figure out the outer helper methods..
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
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
And I guess you can figure out the outer helper methods..
ASKER
@ste5an,
I guess that is my issue, I'm not sure how to ping that VM.
I guess that is my issue, I'm not sure how to ping that VM.
Just try?! Of course you need to be able to access the VM prior to doing anything else.
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.
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.
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).
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).
ASKER
@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.
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.
ASKER
Pinging the IP address from the host did not work. "Request timed out"
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).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
> but no "HostName" listed after issuing the SET command.
Look for COMPUTERNAME.
Look for COMPUTERNAME.
ASKER
@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
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
You can as a work around put an entry in your HOSTs file for the VM.
It's in:
C:\Windows\System32\driver s\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.
It's in:
C:\Windows\System32\driver
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.
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.
ASKER
still working on this guys, don't close the question
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.
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).
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).
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.
ASKER
Sorry, guys, this fell off my radar. Hope this resolves the issue.
ASKER