Author Topic: Setting Up An Internet Database  (Read 6945 times)

0 Members and 1 Guest are viewing this topic.

DHOPP

  • Guest
Re: Setting Up An Internet Database
« Reply #15 on: November 05, 2007, 08:51:39 AM »
I am very interested in how this turns out, I have been asked many times to create a DB outside of excel.   Problem is I can't read the information on VBA fast enough/find tutorials that I 'think' will help me out.

Thanks for creating this thread, I appreciate the chance to learn how to do this.

Draftek

  • Water Moccasin
  • Posts: 1501
Re: Setting Up An Internet Database
« Reply #16 on: November 07, 2007, 12:22:35 PM »
If your internet clients need to push any data, I'd recommend setting up an xml web service and using that for your db connection. That way you can build the same inteface for in-house as across the internet as long as you can deploy the app.

If they only need to use the data then you could still use a db(Access or whatever) and port it to xml. Xml is easily read from a url.

The trick is to n-tier the app so you have some flexibility, especially if you need to scale this puppy up later and if it works, you most likely will. The purpose of n-tier is to isolate functionality so you can add / update easily. For example, don't write any db stuff in your interface. Use a seperate module (dll) for that. Make sure you seperate the connection type and it will never matter what format the database is in. You can set it up to use access now and then sql or xml later with a simple connection string change.

Avoid later pain now by planning. Not that I have any experience with that :)
Soli Deo Gloria

deegeecees

  • Guest
Re: Setting Up An Internet Database
« Reply #17 on: November 07, 2007, 12:32:11 PM »
The trick is to n-tier the app so you have some flexibility, especially if you need to scale this puppy up later and if it works, you most likely will. The purpose of n-tier is to isolate functionality so you can add / update easily. For example, don't write any db stuff in your interface. Use a seperate module (dll) for that. Make sure you seperate the connection type and it will never matter what format the database is in. You can set it up to use access now and then sql or xml later with a simple connection string change.


Thanks Draftek!

I've never written/compiled a DLL before, and I've only dabbled in VB6 (student copy on my Win98 box at home), so if you could give me a point in the right direction, maybe a sample of some type, that would be extremely helpful. I like the idea of having the option to change data types down the line.

Possibly, a quick outline what would need to be done?

Draftek

  • Water Moccasin
  • Posts: 1501
Re: Setting Up An Internet Database
« Reply #18 on: November 07, 2007, 01:57:43 PM »
You don't HAVE to write a dll but when I was using VB6 that is what I did and it made updates and writing new apps easier.

You group your dll's by methodology and when you need that functionality in a new app you merely reference it and you have all or your previously written code available. For instance, I kept my fileIO in one dll and DAO in one, math in one, etc...

I have used some of those dll's in my new .Net code too - for instance I needed a popup menu for a treeview node and I already had working code so...

It's been a while since I used vb but here goes -
1. start a new project of ActiveX dll type. Name it something useful - I like similar prefixes so when I'm adding references they automatically group together - haFiles, haMath, haAutoCAD, etc...
2. Write the necessary code in your classes.
3. compile and use.

The best way to debug a dll is to add another exe project into your application - set it to the startup and reference the dll class in that one. Set a breakpoint in the dll and go to town.

You should also be aware of versioning if you are going to deploy this. It's not rocket science and I never had a problem by just following a few rules - you know, the ones autodesk breaks every 3 releases.......

The above describes how to make utility dll type projects. You can also write vb6 dll code to work inside autocad which gives you all the tools of a compiled vb6 application that runs just like vba does...
Soli Deo Gloria

deegeecees

  • Guest
Re: Setting Up An Internet Database
« Reply #19 on: November 07, 2007, 02:12:31 PM »
Quote
2. Write the necessary code in your classes.

This is where it gets murky. Would I put connection strings in this, and then call those from an AutoCad VBA app with reference set to it?

I think I'm starting to remember going over something similar as this, but the train never left the station on that project. Without jumping into this yet, my old (hack) way of doing things with databases was to simply hardcode the location of the database, and reuse code in multiple modules for their respective functions, it's just the curse of the self-educated I guess. I'll try out your example when I get home and see if I can get something started.

Thanks again Draftek.

 :-)

Draftek

  • Water Moccasin
  • Posts: 1501
Re: Setting Up An Internet Database
« Reply #20 on: November 07, 2007, 03:15:09 PM »
Here's a sample to get you started - An old class I created for adding pop up menus to any windows forms item. I used the class utility wizard in vb6. The winapi code might look intimidating but you can get the idea for adding properties, methods and funtions to your objects:
Code: [Select]
Option Explicit
' cPopMenu class
' Simple API class to create and track popup menus on forms or controls
' C. Hatcher
' 2/17/04

Private Type MENUITEMINFO
cbSize As Long
fMask As Long
fType As Long
fState As Long
wID As Long
hSubMenu As Long
hbmpChecked As Long
hbmpUnchecked As Long
dwItemData As Long
dwTypeData As String
cch As Long
End Type

Public Type POINTAPI
x As Long
y As Long
End Type

Private Declare Function CreatePopupMenu Lib _
"user32" () As Long

Private Declare Function GetCursorPos Lib "user32" _
(lpPoint As POINTAPI) As Long

Private Declare Function TrackPopupMenuEx Lib "user32" _
(ByVal hMenu As Long, ByVal un As Long, _
ByVal n1 As Long, ByVal n2 As Long, _
ByVal hWnd As Long, lpTPMParams As Any) As Long

Private Declare Function InsertMenuItem Lib "user32" _
Alias "InsertMenuItemA" (ByVal hMenu As Long, _
ByVal un As Long, ByVal bool As Long, _
lpcMenuItemInfo As MENUITEMINFO) As Long

Private Declare Function DestroyMenu Lib "user32" _
(ByVal hMenu As Long) As Long

Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private Const MF_STRING = &H0&
Private Const TPM_RETURNCMD = &H100&
Private Const MIIM_ID = &H2
Private Const MIIM_TYPE = &H10
Private Const MIIM_DATA = &H20

Private m_lngMnu As Long
Private m_lngID As Long
Private m_PT As POINTAPI
Private m_objMNU As MENUITEMINFO
'local variable(s) to hold property value(s)
Private mvarmnuName As String 'local copy
'local variable(s) to hold property value(s)
Private mvarItemCount As Integer 'local copy
'local variable(s) to hold property value(s)
Private mvarFirstItem As Integer 'local copy

Public Property Get FirstItem() As Integer
'used when retrieving value of a property, on the right side of an assignment.
'Syntax: Debug.Print X.FirstItem
FirstItem = mvarFirstItem
End Property

Public Property Get ItemCount() As Integer
'used when retrieving value of a property, on the right side of an assignment.
'Syntax: Debug.Print X.ItemCount
ItemCount = mvarItemCount
End Property

Public Property Get mnuName() As String
'used when retrieving value of a property, on the right side of an assignment.
'Syntax: Debug.Print X.mnuName
mnuName = mvarmnuName
End Property

Public Property Let mnuName(sName As String)
mvarmnuName = mnuName
End Property

' a public function to get the current position of the mouse
Public Function GetCursorPosition(lpPointIn As POINTAPI) As Long
GetCursorPosition = GetCursorPos(lpPointIn)
m_PT = lpPointIn
End Function

Public Function CreateSingleMenu(insName As String) As Long
        m_lngMnu = CreatePopupMenu()
        Dim lCount As Long

        Dim inStrNames(1 To 1) As String
        inStrNames(1) = insName
       
        mvarItemCount = UBound(inStrNames)
        mvarFirstItem = LBound(inStrNames)
        For lCount = mvarFirstItem To mvarItemCount
        With m_objMNU
            .cbSize = Len(m_objMNU)
            .fMask = MIIM_TYPE Or MIIM_ID Or MIIM_DATA
            .dwTypeData = inStrNames(lCount)
            .cch = Len(inStrNames(lCount))
            .fType = MF_STRING
            .wID = lCount
        End With
        Call InsertMenuItem(m_lngMnu, lCount, 1, m_objMNU)
        Next lCount
End Function


Public Function CreateMenu(inStrNames() As String) As Long
    m_lngMnu = CreatePopupMenu()
    Dim lCount As Long
    mvarItemCount = UBound(inStrNames)
    mvarFirstItem = LBound(inStrNames)
    For lCount = mvarFirstItem To mvarItemCount
    With m_objMNU
    .cbSize = Len(m_objMNU)
    .fMask = MIIM_TYPE Or MIIM_ID Or MIIM_DATA
    .dwTypeData = inStrNames(lCount)
    .cch = Len(inStrNames(lCount))
    .fType = MF_STRING
    .wID = lCount
    End With
    Call InsertMenuItem(m_lngMnu, lCount, 1, m_objMNU)
    Next lCount
End Function

Public Function TrackMenu(ByVal x As Long, ByVal y As Long, ByVal lngHwnd As Long) As Long
    TrackMenu = TrackPopupMenuEx(m_lngMnu, TPM_RETURNCMD, x, y, lngHwnd, ByVal 0&)
End Function
Soli Deo Gloria

Draftek

  • Water Moccasin
  • Posts: 1501
Re: Setting Up An Internet Database
« Reply #21 on: November 07, 2007, 03:29:13 PM »
oaky, here is one that might be easier to follow - a class I crated for working with bounding boxes of autocad objects. Handy for blind arrangements such as automagically creating layouts for an object or sets of objects. This one was part of my autocad tools library dll.

cBBox.cls
Code: [Select]
Option Explicit
' class: cBBox
' a small class to work with bounding boxes
Const X = 0
Const Y = 1
Private cEntity As AcadEntity
Private varMax As Variant
Private varMin As Variant

'Get the Object we need to work with
' Note: Why do I feel compelled to write my comments in plural?
Public Property Set bbEntity(objEntity As AcadEntity)
    Set cEntity = objEntity
    ' might as well get the bounding box here because
    ' we're gonna need for everything
    cEntity.GetBoundingBox varMin, varMax
End Property

' the overall length
' read-only
Public Property Get xWidth() As Double
    Dim dWidth As Double
    ' I'm sure this is not necessary, but JUST IN CASE
    If varMax(X) > varMin(X) Then
        dWidth = varMax(X) - varMin(X)
    Else
        dWidth = varMin(X) - varMax(X)
    End If
    xWidth = dWidth
End Property

' the overall height
' read-only
Public Property Get yHeight() As Double
    Dim dHeight As Double
    ' I'm sure this is not necessary, but JUST IN CASE
    If varMax(Y) > varMin(Y) Then
        dHeight = varMax(Y) - varMin(Y)
    Else
        dHeight = varMin(Y) - varMax(Y)
    End If
    yHeight = dHeight
End Property

' the area
' read-only
Public Property Get bbArea() As Double
    Dim dArea As Double
    Dim dHeight As Double
    Dim dWidth As Double
    dHeight = Me.yHeight
    dWidth = Me.xWidth
    dArea = dHeight * dWidth
    bbArea = dArea
End Property

' the center point
' read-only
Public Property Get CenterPoint() As Variant
    Dim Ipt(0 To 2) As Double
    ' set the center point
    Ipt(0) = ((varMax(0) - varMin(0)) / 2) + varMin(0)
    Ipt(1) = ((varMax(1) - varMin(1)) / 2) + varMin(1)
    CenterPoint = Ipt
End Property

Private Sub Class_Terminate()
    On Error Resume Next
    Set cEntity = Nothing
End Sub
Soli Deo Gloria

deegeecees

  • Guest
Re: Setting Up An Internet Database
« Reply #22 on: November 04, 2010, 12:22:10 PM »
After 3 years and 197 full proposals for the company, I'm finally back to this.

What I'm trying to do:
1. I have an xls document with pricing for our products, I want to port this info to some form on our web server (suggestions?)
2. Export ACAD Table to an Excel spreadsheet and query the web server for matching info to pull in the prices to the spreadsheet.

I'm reading quite a bit about SQL, and such but most of the knowledge I acuired when I started this 3 years ago has evaporated from my head. I just need a jump start as to which way to go and how to get started (again). I'm in the noob stage atm.

Questions:
- Could I use an ACCESS database for this?
- How do I retrieve the data from our web server?

Any help on getting things going would be greatly appreciated (again).

n.yuan

  • Bull Frog
  • Posts: 277
Re: Setting Up An Internet Database
« Reply #23 on: November 04, 2010, 01:40:22 PM »
I am sorry to say that most discussions so far on this thread shows the lack of basic understanding on Internet/network knowledge.

Accessing data over the Internet is very different from access data (file or sort of database) in your own LAN. Well, you may set up VPN connection so that the connection to the Internet is as if you connect to your own LAN, but in most cases, the nature of connecting to the Internet makes it very different from the ordinary process, in which you use code in Acad VBA to connect Excel/Access.

In short, if you have to host the data (ususally the data should be in database server of some kind, not a file, or file based database, such as Access, even they can be used in certain condition) over the Internet, then your or someone has to develop a web service (as a sort of remote DLL), and you can use SOAP Tool kit in VBA to consume the web service.

Using VB6/VBA to consume web services is no trivial work. If you use Acad's .NET API, it would be a lot easier to consume web services/WCF services from inside AutoCAD.

However, your main issue would be to develop web services/WCF services that can be hosted somewhere in the Internet (or even your own companies intranet) as the data tier of application.


After 3 years and 197 full proposals for the company, I'm finally back to this.

What I'm trying to do:
1. I have an xls document with pricing for our products, I want to port this info to some form on our web server (suggestions?)
2. Export ACAD Table to an Excel spreadsheet and query the web server for matching info to pull in the prices to the spreadsheet.

I'm reading quite a bit about SQL, and such but most of the knowledge I acuired when I started this 3 years ago has evaporated from my head. I just need a jump start as to which way to go and how to get started (again). I'm in the noob stage atm.

Questions:
- Could I use an ACCESS database for this?
- How do I retrieve the data from our web server?

Any help on getting things going would be greatly appreciated (again).

deegeecees

  • Guest
Re: Setting Up An Internet Database
« Reply #24 on: November 04, 2010, 01:48:36 PM »
Quote
I am sorry to say that most discussions so far on this thread shows the lack of basic understanding on Internet/network knowledge.

No argument there. I'm completely new to this.

Quote
Accessing data over the Internet is very different from access data (file or sort of database) in your own LAN. Well, you may set up VPN connection so that the connection to the Internet is as if you connect to your own LAN, but in most cases, the nature of connecting to the Internet makes it very different from the ordinary process, in which you use code in Acad VBA to connect Excel/Access.

In short, if you have to host the data (ususally the data should be in database server of some kind, not a file, or file based database, such as Access, even they can be used in certain condition) over the Internet, then your or someone has to develop a web service (as a sort of remote DLL), and you can use SOAP Tool kit in VBA to consume the web service.

Using VB6/VBA to consume web services is no trivial work. If you use Acad's .NET API, it would be a lot easier to consume web services/WCF services from inside AutoCAD.

However, your main issue would be to develop web services/WCF services that can be hosted somewhere in the Internet (or even your own companies intranet) as the data tier of application.

Got any pointers/resources to get me going in the right direction?

n.yuan

  • Bull Frog
  • Posts: 277
Re: Setting Up An Internet Database
« Reply #25 on: November 04, 2010, 02:20:01 PM »
Firstly, geting off-site user to access business reources (data) needs proper network infrastructure, which comes with cost, (be it via VPN, or web server hosted in the Internet, or web server hosted in your internel LAN, which than open to the Intenet) with necessary knowledge and skill. So, my advice is that if yor company has qualified network professinal, consult him/her first for the viability of getting external user to connect to the data hosted by a web server;

Secondly, if your company is too small to host its own web server and develop ist own web services/WCF services, looking for a Internet service provider who not only host a web server/services, but also develop custom web services (most good ones do). In most cases, it is a lot cheaper than you do the whole thing on your own.

Thirdly, if there is only a few external users, using VPN connection from a service provider to even remote desktop might be even easier solution. In this case, you still develop your app/database as if in your own LAN as you do so far, and just pay service fee to service provider to allow remote user to "remote in".

If you really want to develop your own solution with data being hosted in the Internet, you'd better start learning the Internet programming. If you choose MS platform, you'd better move on to .NET programming: although VB6/VBA can be used to develop web services, it is an ancient technology, no one uses it amy more for this purpose. Pick up almost any basic .NET programming book, it will include topic on web services/WCF services development. And you still has to understand lots of network connection issue (connecting a private LAN to a public Internet safely and efficiently is never an easy thing to do). If your company is big enough and connected with fast-enough network to multiple office, it is also good to have front user application to connect data hosted by web/WCF services rather than directly to a data file/database.

Finally, whether you can do it on your own or not currently, you do see the need. You may want to move to Acad .NET API programming (and then expand your interest on generic .NET programming). As you may already know, VBA in Acad is dead, you do not want to spend a lot time to build the solution betting on VBA.

deegeecees

  • Guest
Re: Setting Up An Internet Database
« Reply #26 on: November 04, 2010, 02:27:57 PM »
I just realized this thread is in the VBA section. That was my intent 3 years ago. I'm aware of the phasing out of VB, and I'll get crackin on the .NET books.

I appreciate the input.